I'm working with load scripts in Qlik Sense and have encountered a few questions that I don't see directly addressed in the documentation, training modules or forums. In this instance, I'm creating fields related to fiscal periods. There is plenty of information and scripts available for building master calendars, some of which I'm adapting, but none of it completely relates to my approach. I've attached a few rows from the Excel data I'm using.
I started by creating a new section in the Data Load Editor and tried adding DECLARE and DERIVE statements there using the input field [Inspection End Date] to add to the fields created by the generated code. Syntactically valid statements were run (or stepped through in the debugger) and the results of the load were "successful" but I could find no evidence of the new fields. However, simple statements placed after DERIVE worked so I could tell they were being reached on execution.
1) Are DECLARE / DERIVE sensitive to position relative to LOAD?
I've read about sequential LOAD commands but there was nothing about DERIVE. The new section did not have a LOAD itself (it just contained the DECLARE and DERIVE) but followed the autogenerated section that did have the LOAD.
I temporarily sidestepped that issue by unlocking and editing the autogenerated script but clearly that is not what is ultimately needed. The calendar examples and scripts that I've found all rely on LOAD. I may be able to use that, particularly now that I've learned about stacking LOAD commands, however, when I began I thought DERIVE would be a good solution so that is what I've been using. For my purposes, I've converted some of the concepts I've seen from LOAD to DERIVE format. While doing so, I noticed although that the bottom up evaluation of LOAD allows fields defined in one statement to be referenced by expressions in a (textually) preceding LOAD, DERIVE doesn't appear to have analogous functionality.
2) Is there a way to reference field definitions on subsequent (or preceding) lines within a DEFINE statement or must each value be independently computed?
Referencing a field below its definition in DECLARE caused no error but also did not work and I see that the autogenerated script recomputes values like Month($1) wherever needed.
3) Are values strongly or weakly typed? The autogenerated code specifically casts values with Num() but it also uses those results in string concatenation. When are Num() and Text() explicitly needed?
Finally, while trying to create a fiscal year field displaying the spanned calendar years but with the fiscal year value, only some rows were getting populated. I deconstructed my expression and started building it up piece by piece and then exporting the results to validate. I reached a point where the syntax is correct, I see no obvious logic or usage errors and yet the output of Year($1) is clearly incorrect in the context. I've been unable to determine if I'm doing something incorrectly or if I've encountered an evaluation bug. In the attached load script I see the behavior in the field [dfy_test]:
Dual(if(Num(Month($1)) < FyStartMon, '<10 ' & Year($1), '>=10 ' & Year($1)), Num(Month($1))) as [dfy_test],
The display value should indicate which part of the if statement was used and the year component of the original date. The actual data file contains dates from 2008 - 2016. The expression appears to produce correct results for the dates between 10/01/2008 and 09/30/2009. That may be coincidence however because from 10/01/2009 onward, the value returned is always either >=10 2008 or <10 2009.
4) What is happening here?
The working version of the load script is attached along with portions of exported data from a table view of relevant fields.