What is the equivalent of ROW_NUMBER() OVER (PARTITION BY etc... ORDER BY etc... DESC) in Qlik Sense

[Stanislav1]

Hello there!

I have the following issue. I want to recreate this logic into Qlik sense, but without success. What I want is for every combination of ga,download_date to order the results of these 3 different columns DESC to basically see which ga has the earliest download_date with that combination. 

The code: 

ROW_NUMBER() OVER ( PARTITION BY ga, download_date ORDER BY gclid_exists DESC, bing_exists DESC, referrer_exists DESC ) AS RN

Currently I am trying with AutoNumber():

AutoNumber(ga & '_' & download_date & '_' & gclid_exists & '_' & bing_exists & '_' & referrer_exists) as Row_Key  

However, I don't get the desired result. I appreciate any suggestions. Thank you in advance.

[JandreKillianRIC · Accepted solution]

Hi @Stanislav1 

Autonumber should work, But I would remove the &' _'& stuff. 

I would just add a order By on the table: 

ORDER BY gclid_exists DESC, bing_exists DESC, referrer_exists DESC

Check out this post. 

https://community.qlik.com/t5/New-to-Qlik-Analytics/SQL-ROW-NUMBER-OVER-PARTITION-BY-ORDER-BY-Row-Nu...

So the load would look something like this 

Load 
    Field, 
    Field1, 
    Field2, 
    Field3
    Autonumber(ga, download_date) as Row
From xxxxx
Order by gclid_exists DESC, bing_exists DESC, referrer_exists DESC;

Regards - Jandre

[Stanislav1]

Thank you very much, Jandre