have problems when use case conditions

[WuCong]

I have a raw data like below then I create a flag,got the error result.

case([ECSTDAT2 < v10_RETAMT_DADAT3 && ECENDAT2 < v10_RETAMT_DADAT3, DATEDIF(ECSTDAT2, ECENDAT2, day) * ECDOSE2, 0]),it confused me .

[WuCong]

the right result should be 0,38,0,38. so why I got the wrong expected result .

[WuCong]

and sometimes ,I can get the right result use case like this

[ApekshaP · Accepted solution]

Hi Wu Cong,

The case condition is giving 0 means the comparison and AND condition are not true. All the columns seem to be date data type. You can explicitly try to set the date format. There are 2 ways:

1. Change the date datatype : DATEFORMAT(ECSTDAT2, 'dd MMM yyyy')
2. case([DATEFORMAT(ECSTDAT2, 'dd MMM yyyy') < DATEFORMAT(v10_RETAMT_DADAT3, 'dd MMM yyyy') && DATEFORMAT(ECENDAT2, 'dd MMM yyyy') < DATEFORMAT(v10_RETAMT_DADAT3, 'dd MMM yyyy'), DATEDIF(ECSTDAT2, ECENDAT2, day) * ECDOSE2, 0])

There are UNIXTIMEFORMAT and DATEFORMAT functions you can try to see which one leads to the correct answer.

Tip: disintegrate your condition into single steps to find out which AND condition is not giving the correct result. Hope this helps.

Take care and stay safe,

Apeksha

[WuCong]

is there any difference to use in case and if ??

[ApekshaP]

Both are conditional statements with no difference in terms of functionality. When you have a nested condition, then the Case on custom condition (transform) or CASE function makes it easy to follow compared to Nest IF function.