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.