Looking through the Window (function)

Recently, Qlik quietly announced two new functions available in Qlik SaaS – Window and WRank. It would be easy for these to pass under the radar and not get adoption with Qlik developers, but they actually represent a real quality of life question. If you’re used to using SQL Window functions (e.g. OVER, PARTITION BY), then this will be familiar to you…

Imagine the scenario where you have a list of changes made to some data and you want to flag the most recent for each key. Previously you might have done something like this:

[history_temp]:
Load
    [Key],
    [ChangeDate],
    [Value]
From [lib://:DataFiles/Source.qvd] (qvd);

[latest_map]:
Mapping Load
    [Key] & ‘~’ & Max([ChangeDate]) as [KeyMaxChangeDate],
    1 as [LatestFlag]
Resident [history_temp]
Group by [Key];

[history]:
Load 
    *,
    applymap(‘latest_map’, [Key] & ‘~’ & [ChangeDate],0) as [LatestFlag]
Resident [history_temp];

Drop Table [history];

(Yes, I know there are many ways of doing this – this is just one.)

However now, it’s possible to do this in one single load:

[history]:
Load
    [Key],
    [ChangeDate],
    [Value],
    if([ChangeDate]=Window(Max([ChangeDate]),[Key]),1,0) as [LatestFlag]
From [lib://:DataFiles/Source.qvd] (qvd);

What it’s effectively doing is a “group by” on the fly and without reducing the granularity, which saves a lot of the resident loads.