Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

Tags (4)
1 Solution

Accepted Solutions
Not applicable

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

3 Replies

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

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

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;

Not applicable

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