Parsing error in IF THEN ELSE case

[imzy66]

Hi all

First time posting and currently work fir a homecare company... but if you see attached, i am trying ti write a query calculation but keep getting a parsing error but i cant seem to resolve.. any thought would be appreciated!!!!!

Thanks in advance!!

Sent from my SM-G925F using Tapatalk

[BigChris]

I've got a sore neck now...

1. What parsing error do you get?
2. Have you tried the count function as a separate field to make sure that works for you? I haven't use a WHEN clause in that sort of thing before, but that might not mean anything.
3. Do you need another ) before the Else clause?

[imzy66]

Sorry for the picture


Error code: QE-DEF-0260

No i havnt i have tried seperatly and same error, notsure how i can word/replace the word 'when'

Dont believe need more or less brackets seems to me for every one i open i have then closed :s

Sent from my SM-G925F using Tapatalk

[Lynn]

Can you copy/paste the expression in your post?

I don't think the "when" clause is valid with an "if" statement. You'll need to use a case statement instead of "if" or stick with "if" but consider using "and" to combine multiple conditions.

It is difficult to offer a specific suggestion unless you can post the actual expression.

When I need to use a summary function in a conditional manner, I find it works best to put the condition inside the function rather than the function inside the condition.

count ( distinct case when {some condition is true} then [Your query item] else null end )

You might also consider whether order required delivery date is a date or a date/time data type. You are comparing to a date/time literal which may not be valid if the field only holds a date. Of course hard coding the date/times seems a bit rigid unless you will never need to worry about anything that happened after March 31st.

[imzy66]

I will at somepoint write the actuall expression and what i am trying to achieve by tuesday latest and hope you can offer more of a suggestion then if thats ok lynn.. appreciate your thoughts... i will place a cast function on order required delivery date to make all fields be same format as in date only and will be back in touch!

Sent from my SM-G925F using Tapatalk

[imzy66]

So my expression i currently have :-

If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)

All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...

Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors

Appreciate any help

Thank you

Sent from my SM-G925F using Tapatalk

[MFGF]

So my expression i currently have :-

If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)

All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...

Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors

Appreciate any help

Thank you

Sent from my SM-G925F using Tapatalk

How about:

If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000 and [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists'))
THEN
(Count(distinct [order crm number]))
ELSE
(0)

[Lynn]

So my expression i currently have :-

If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)

All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...

Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors

Appreciate any help

Thank you

Sent from my SM-G925F using Tapatalk

Both BigChris and I provided some suggestions. Which of those things have you tried already?

What I find works best is what I previously suggested:
count ( distinct case when {some condition is true} then [Your query item] else null end )

This will return either the thing you want to count or null. The count function will ignore nulls.

Code Select Expand


Count(distinct
  case
    when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists' )
     and [order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000
    then [order crm number]
    else null
  end     
)


[imzy66]

Simple and perfect, just re-arranging equation made this work! Thank you

Sent from my SM-G925F using Tapatalk

[imzy66]

I dine as mfgf and all seems to be fine just testing results so will keep you posted

Sent from my SM-G925F using Tapatalk

[MFGF]

I dine as mfgf and all seems to be fine just testing results so will keep you posted

Sent from my SM-G925F using Tapatalk

I think Lynn's solution is probably more efficient, and it's certainly more elegant :)

MF.

[BigChris]

I think Lynn's solution is probably more efficient, and it's certainly more elegant :)

Generally the case I find...

[imzy66]

So i have created the query calculation with in the query i then checked repirt validation and all seems fine but as soon as i put the data item (query calculation) im getfing the following error:-

Uda-ee-0094 the operation "greater_equal"is invalid for the following combination of data types"date2" and "character"


So the only other thing i am trying to diplay with the calulation is names

Sent from my SM-G925F using Tapatalk

[MFGF]

So i have created the query calculation with in the query i then checked repirt validation and all seems fine but as soon as i put the data item (query calculation) im getfing the following error:-

Uda-ee-0094 the operation "greater_equal"is invalid for the following combination of data types"date2" and "character"


So the only other thing i am trying to diplay with the calulation is names

Sent from my SM-G925F using Tapatalk

I'd be checking what data type [order required delivery date] is defined as in your database / model.

MF.

[imzy66]

Update, used cast function on order required delivery date and report now working fine! Thank you for your help!

Sent from my SM-G925F using Tapatalk