Rank Function and Partition by multiple dimension and apply filter on the rank

[CK_WAKE]

Hi There,I have n number of columns in the table and I would like to apply rank function on below variables based on latest survey date where i can filter out rank = 1.

Appreciate your advice.

Something similar to SQL Query.

rank() over (partition by COUNTRY, STATE order by survery_date desc)

from table

where rank = 1

[Vegar]

What about something like I 've done in the sample script below?

LOAD *
WHERE max_survey_date = survery_date;
LOAD
   id,
   country,
   state,
   survery_date,
   Window( max(survery_date), country,state ) as max_survey_date
INLINE [
id, country, state, survery_date
1, Sweden, Dalarna, 2024-05-01
2, Sweden, Jämtland, 2024-05-01
3, Sweden, Dalarna, 2024-05-03
4, Sweden, Jämtland, 2024-05-05
5, Sweden, Dalarna, 2024-05-04
6, Norway, Sor-Trondelag,2024-05-02
7, Norway, Sor-Trondelag,2024-05-05
8, Sweden, Dalarna, 2024-05-02
9, Sweden, Dalarna, 2024-05-01
10, Sweden, Jämtland, 2024-05-02
11, Norway, Sor-Trondelag,2024-05-06
12, Sweden, Dalarna, 2024-05-01
13, Sweden, Jämtland, 2024-05-03
14, Norway, Sor-Trondelag,2024-05-04
15, Sweden, Dalarna, 2024-05-01
16, Sweden, Jämtland, 2024-05-04
17, Sweden, Dalarna, 2024-05-01
18, Sweden, Dalarna, 2024-05-03
19, Sweden, Dalarna, 2024-05-03
20, Norway, Sor-Trondelag,2024-05-01
];

It will produce this output:

[CK_WAKE]

Hi There, thanks for the code. Unfortunately in the below code, WINDOW function in not getting recognised.

inner join(table1)
load
country
WHERE max_survey_date = survey_dt;
Load
country,
state,
survey_dt,
Window( max(survey_dt), country,state) as max_survey_date // this section is not getting recognized(it is getting highlighted in red color)
resident table1;

[Vegar · Accepted solution]

Which Qlik Sense version are you on? It could be that you are using an older version not supporting the window function.

Try this instead 

INNER JOIN (table1)

Load

country, state, max(survey_dt) as survey_dt

RESIDENT table1 

GROUP BY country, state;

[CK_WAKE]

Thanks for the help