As you master QlikView or Qlik Sense, understanding how the script engine works under the hood will not only improve your ability to troubleshoot reload errors but also help you appreciate and marvel the power of the preceding load in your script.
Let’s understand how the script load engine parses the script first.
Then it will be easier to understand why preceding load comes handy when you create multiple transformations in your script.
// 1 Encounters Orders table
Orders:
Load *,
if(Year = Year(today(0)), 1, 0) as InCurretYearFlag
;
// 3 First pass: loads fields without transformations
// 4 Second pass: loads transformed fields
LOAD
OrderID
CustomerID,
EmployeeID,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Day(OrderDate) as Day
where Year(OrderDate)>2016
;
// 2 Loads Orders table data from the relational source
SQL SELECT CustomerID,
EmployeeID,
OrderDate,
OrderID
FROM Orders;
What Happens Under the Hood?
As soon as the script engine encounters a table (Orders), it finds the source first and executes the SQL SELECT statement to fetch data from the relational source.
Next, It finds the first adjacent load statement, and in the first pass, it loads only base fields that match the field names in the SQL SELECT statement. It will not parse newly transformed fields in the first pass, such as Year(OrderDate) as Year.
If you use a WHERE clause, you can’t use a newly transformed field in the WHERE clause. Instead, you have to use the expression itself in the WHERE clause (WHERE Year(OrderDate) > 2016).
For the understanding sake, imagine the script engine parsing the same load statement again to handle newly transformed fields next.
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Day(OrderDate) as Day
Upon completion of the second pass through the same load statement, the script engine reads the load statement adjacent to the current load statement.
Orders:
Load *,
if(Year = Year(today(0)), 1, 0) as InCurretYearFlag
;
Since a transformed field Year already exists in memory, the preceding load statement can now use it in the additional transformation to create the InCurrentYear flag.
So, in a nutshell, the script engine runs from top to bottom, but for a given load statement, it runs from bottom up.
Summary
A good understanding of how the script engine executes a table load statement will help you visualize the script load execution flow and also help troubleshoot any errors you may encounter during the load.
(Photo by dylan nolte on Unsplash)
