2 Replies Latest reply: Jun 29, 2014 9:54 AM by Stefan Wühl RSS

    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;

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

            • Re: Creating a date field from two other fields
              Stefan Wühl

              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