T-SQL Ranking Function logic in Qlik Sense load script?

[Huxleys]

Hi,

I currently work with a QVD copy of our OLTP SQL database - Source Layer has QVD tables for every table in SQL databases. I'm attempting to transform a table to fit my data model, and in T-SQL I would use a ranking function like:

WITH temp AS

(

SELECT BuildID,

StatusID,

UserID,

DateTime,

Units,

Row_Number() OVER (PARTITION BY BuildID ORDER BY  DateTime DESC) AS rnk

FROM dbo.BuildStatus

WHERE CAST(DateTime AS Date) = CAST(GetDate() AS Date)

)

SELECT *

FROM temp

WHERE rnk=1

ORDER BY BuildID DESC

This would give me a record for each BuildID with the most recent StatusID (I do not need the historical changes to StatusID as this is a real-time dashboard for production and the Source Layer reloads every minute with incremental load).

How could I achieve this strictly in the Qlik load script with Qlik syntax, as I am not directly querying a SQL table and I am instead transforming a QVD?

[SerhanKaraer · Accepted solution]

Hello Huxleys,

First load the qvd into memory as below:

Source:
LOAD BuildID, StatusID, UserID, DateTime, Units FROM YourQvdFilePath (qvd)
WHERE Floor(DateTime) = Today();

Then, first order the table in the memory and then apply ranking as below, afterwards apply filter for getting the highest ranks.

Ranked:
NoConcatenate
LOAD * WHERE rnk = 1;
LOAD BuildID, StatusID, UserID, DateTime, Units, AutoNumber(RecNo(),BuildID) as rnk Resident Source ORDER BY BuildID ASC, DateTime DESC;

Drop Table Source;

[Huxleys]

Brilliant - thank you! I'll keep the combination of AutoNumber and preceding load in mind for the future.