calculated field in data load script not available

[jbchurchill]

At the end of my data load section in the data load script, I've got this bit of code (not working).

    If(IsNull("close_date"), '', Date(Date#("close_date") - open_date)) as new_days_open,
    if("new_days_open" < 14, 'True', 'False') as FlagOnTime;

The first line works but that very last line isn't doing what I expected. Is the syntax incorrect or is the calculated "new_days_open" field unavailable prior to the completed data load?

[GaryGiles · Accepted solution]

The new_days_open field would not be available for use in the 2nd second statement.

You could rewrite the 2nd if, like:

if(IsNull("close_date"), 'False', if(Date(Date#("close_date") - open_date)< 14, 'True', 'False')) as FlagOnTime;

Not sure how you want to handle FlagOnTime if "close_date" is null.  True or False?

[jbchurchill]

Thanks for the reply. True or False would probably be best. Will I be able to use the True() and False() functions here? ... or should I just use a string like I did in my non-working code sample?

[rwunderlich]

Yes, the "new_days_open" field is not yet available, but it would be if you used the Preceding Load feature. 

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/prece...

In your case, something like:

MyData:
LOAD
  *, 
  if("new_days_open" < 14, 'True', 'False') as FlagOnTime
;
LOAD
  blah, blah,
  If(IsNull("close_date"), '', Date(Date#("close_date") - open_date)) as new_days_open
FROM ....
;

You can use strings for your flags, but the typical method is to use the default Qlik values: -1 for true, 0 for false. In which case you can create FlagOnTime like 

"new_days_open" < 14 as FlagOnTime

Most people make the  -1 (true) flags as 1 so I can Sum() or multiply. To do that just add - to the expression. 

- "new_days_open" < 14 as FlagOnTime

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[jbchurchill]

I see what you are saying. I'd just need to add a second load statement in the same script (didn't think of that either). I have been able to do what @GaryGiles suggested. 

I probably should have mentioned that I'm leading up to using this in a stacked bar (hopefully with a third measure). The two sections of the bar will have the remaining records from the flagOnTime value for the ones that were not on time.

Thanks for the info.