Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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:
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;
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;
Thanks for the help