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

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

Thu, 18 Sep 2014 12:34:33 GMT

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

rubenmarin — Thu, 18 Sep 2014 12:37:39 GMT

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

MK_QSL — Thu, 18 Sep 2014 12:43:33 GMT

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

Fri, 19 Sep 2014 11:35:52 GMT

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