topic Creating a date field from two other fields in QlikView

Creating a date field from two other fields

Sun, 29 Jun 2014 12:49:47 GMT

Hi,

I have 4 possible dates that I want the script to select one of the 4 and place it into a new date column. I attempted the if statement below but keep getting -1 as the value for all my dates.

CHURN_CAL:

LOAD

WRKF_KEY,

IF(ACC_NEW_HIRE_FLG = 'Y','A',IF(SEP_ALL_FLG = 'Y','S')) as CHURN_FLG,

date(IF(SEP_ALL_FLG = 'Y' AND END_DT < today(),END_DT,SEP_DT) OR

IF(ACC_NEW_HIRE_FLG = 'Y' AND START_DT > ACC_STRT_DT, START_DT,ACC_STRT_DT),'MM/DD/YYYY') AS CHURN_DATE

Resident Tmp1;

Re: Creating a date field from two other fields

Sun, 29 Jun 2014 13:30:44 GMT

***Update***

I got the script below to work as desired but seems a bit clunky. Any suggestions?

CHURN_CAL:

LOAD

WRKF_KEY,

IF(ACC_NEW_HIRE_FLG = 'Y','A',IF(SEP_ALL_FLG = 'Y','S')) as CHURN_FLG,

date(IF(SEP_ALL_FLG = 'Y' AND date(END_DT) < today(),date(END_DT),

IF(SEP_ALL_FLG = 'Y' AND date(END_DT) > today(),date(SEP_DT),

IF(ACC_NEW_HIRE_FLG = 'Y',date(START_DT)))),'MM/DD/YYYY') AS CHURN_DATE

Resident Tmp1;

DROP Table Tmp1;

Re: Creating a date field from two other fields

swuehl — Sun, 29 Jun 2014 13:54:42 GMT

I believe you don't need to format dates inside your if() statement, just keep the formatting of the result:

date(

IF(SEP_ALL_FLG = 'Y' AND END_DT < today(), END_DT,

IF(SEP_ALL_FLG = 'Y' AND END_DT > today(), SEP_DT,

IF(ACC_NEW_HIRE_FLG = 'Y', START_DT )))

,'MM/DD/YYYY')

AS CHURN_DATE