have problems when use case conditions

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 .

Trifacta Classic

Accepted answers

ApekshaP

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

All comments

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

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

WuCong

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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335