topic Re: have problems when use case conditions in Alteryx Designer Cloud Discussions

have problems when use case conditions

WuCong — Tue, 19 Apr 2022 12:57:44 GMT

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 .

Re: have problems when use case conditions

WuCong — Tue, 19 Apr 2022 12:58:45 GMT

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

Re: have problems when use case conditions

WuCong — Tue, 19 Apr 2022 12:59:04 GMT

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

Re: have problems when use case conditions

ApekshaP — Thu, 21 Apr 2022 06:00:33 GMT

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:

Change the date datatype : DATEFORMAT(ECSTDAT2, 'dd MMM yyyy')
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

Re: have problems when use case conditions

WuCong — Thu, 21 Apr 2022 06:42:52 GMT

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

Re: have problems when use case conditions

ApekshaP — Thu, 21 Apr 2022 08:34:41 GMT

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.