October 13, 2023 By Alex Walker

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:

From [lib://:DataFiles/Source.qvd] (qvd);
Mapping Load
  [Key] & ‘~’ & Max([ChangeDate]) as [KeyMaxChangeDate],
  1 as [LatestFlag]
Resident [history_temp]
Group by [Key];
  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:

    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.


About Author

Alex Walker

Since joining Ometis as employee number three in 2012, Alex has worked with every one of our customers and every product, as well as implemented and improved our internal systems. He loves keeping up with the latest and greatest technology and gadgets, whether Qlik-related or in the broader IT industry. You'll see many of his ideas and comments shared on the Qlik Community.

Subscribe and get the latest
news, updates and best practices