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

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

Stanislav1 — Thu, 23 Jan 2025 09:37:20 GMT

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.

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

JandreKillianRIC — Thu, 23 Jan 2025 13:17:21 GMT

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-Number-in-Qlik/td-p/2140322

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

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

Stanislav1 — Wed, 05 Feb 2025 08:17:33 GMT

Thank you very much, Jandre