In a line-level table, there are multiple records stored for a single dimensional value. For example, an Order Line
table will have multiple lines for the same OrderID
. Business requirements may warrant us to only consider the first or the latest line for each order. This can be done using the Previous()
function available in Qlik Sense.
For the sake of continuity, we will make use of the same script and application as in the previous recipe. We will determine the most recent position for any employee during his or her tenure within the organization.
EmployeeInt
table in the script to EmployeeIntTemp
.EmployeeIntTemp
table. If you are copying and pasting the code in the data load editor, make sure that the single quotes are copied in a proper format:EmployeeInt: LOAD *, if([EmployeeID]= previous([EmployeeID]),'No','Yes') AS LatestRecordFlag RESIDENT EmployeeIntTemp ORDER BY [EmployeeID] ASC, PositionFrom DESC; DROP TABLE EmployeeIntTemp;
LatestRecordFlag
in the Table object we created in the previous recipe.PositionFrom
is promoted to the top. Switch off the Auto sorting feature for PositionFrom
. No sorting options should be selected as this will then show the PositionFrom
date in the load order.LatestRecordFlag
value as Yes, it will only show the latest position for Susan: HR Director.The LatestRecordFlag
can be used in calculations to determine the most recent position of any employee. In our script, we create the LatestRecordFlag
using the Previous()
function. The Previous()
function basically parses the EmployeeID
column. If the current record that is being read has the same EmployeeID
value as the previous record, then it is flagged as No
or else Yes
.
The ordering of the fields plays an important role here. Because I wanted to determine the latest position for the employee, the field PositionFrom
is arranged in descending order.
We can also make use of the Peek()
function in the preceding script. In our example, both Peek()
and Previous()
would yield the same result. However, Peek()
is more effective when the user is targeting a field which has not previously loaded in the table or if the user wants to target a specific row. The Previous()
function is more effective when the user wants to compare the current value with the previous value for the field in the input table.