Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creating a date field from two other fields

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;

2 Replies
Not applicable

Re: Creating a date field from two other fields

***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;

MVP
MVP

Re: Creating a date field from two other fields

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