3 Replies Latest reply: Sep 19, 2014 7:35 AM by Ian Turner RSS

    Problem with Load Script and using field I have created.. with nested if statement

      Hi, I am new to QlikSense and loving it .. however I have come to a bit of a roadblock with the following

       

          if( date("Last Email Received") < date("Last Email Sent"),

          if(date("Last Email Received") <  date("Last Activity"), 

              date("Last Activity"), date("Last Email Sent")),

                date("Last Email Received")) as activity_date,

                 

          Interval(now() - activity_date,'d') as days_since_activity,

          year(activity_date) as activity_date_year,

          month(activity_date) as activity_date_month,

       

      I have 3 dates and my intention is to put the most recent date of the 3 in a field called activity_date then work out the number of days from now to that date.   I think the syntax of the embedded if statements is correct, all the braces match up ...

       

      When I load the script, I get a "Field not found - <activity_date>"

       

      Thanks

      Ian

        • Re: Problem with Load Script and using field I have created.. with nested if statement
          Ruben Marin

          HI Ian,

           

          Split the Load, activity_date doesn't exist until the first load, use someting like:

          LOAD *,

              Interval(now() - activity_date,'d') as days_since_activity,

              year(activity_date) as activity_date_year,

              month(activity_date) as activity_date_month;

          LOAD

          if( date("Last Email Received") < date("Last Email Sent"),

              if(date("Last Email Received") <  date("Last Activity"), 

                  date("Last Activity"), date("Last Email Sent")),

                    date("Last Email Received")) as activity_date

          ...

          • Re: Problem with Load Script and using field I have created.. with nested if statement
            Manish Kachhia

            You can't use any field name which you have renamed in the same table...

            In your case, you have to use as below

            Load *,

                Interval(now() - activity_date,'d') as days_since_activity,

                year(activity_date) as activity_date_year,

                month(activity_date) as activity_date_month;

            Load *,

              if( date("Last Email Received") < date("Last Email Sent"),

                if(date("Last Email Received") <  date("Last Activity"),

                    date("Last Activity"), date("Last Email Sent")),

                      date("Last Email Received")) as activity_date

            From TableName;

            • Re: Problem with Load Script and using field I have created.. with nested if statement

              Thanks .. that worked .. i did have the logic in the if() wrong and chose to just use the if function twice .. as follows.  It does feel a little strange that a field created can not be re-used later in the script - thats a big learning point for me ! 

               

              Here is what worked:

               

                  if ( date("Last Email Received") > date("Last Email Sent"),

                  if (date("Last Email Received") > date("Last Activity"), date("Last Email Received") ,

                      if(date("Last Activity") > date("Last Email Sent"), date("Last Activity") ,date("Last Email Sent") ))) as activity_date,

                

                  Interval(now() -

                  if ( date("Last Email Received") > date("Last Email Sent"),

                  if (date("Last Email Received") > date("Last Activity"), date("Last Email Received") ,

                      if(date("Last Activity") > date("Last Email Sent"), date("Last Activity") ,date("Last Email Sent") )))

                   ,'d') as days_since_activity

               

              Thanks

              Ian