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

[#1]

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

[rubenmarin]

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

...

[MK_QSL]

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;

[#4 · Accepted solution]

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