Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need assistance in terms to develop a pivot table with Rank function:
Requirement:
We have a map where we are plotting Rank City wise based on Score.
If a particular user click on a specific city, a pivot table will be shown with the rank of the selected city in comparision to the rest of cities
Data is as follows:
Year | City | Level1 | Level2 | Level3 | Level4 | Score |
2015 | ABC | Enb | PE & SE | PE | Q1 | 84.62 |
2015 | ABC | Enb | PE & SE | PE | Q2 | 90.35 |
2015 | ABC | Enb | PE & SE | PE | Q3 | 80.00 |
2015 | ABC | Enb | PE & SE | EG | Q4 | 58.82 |
2015 | ABC | Enb | PE & SE | EG | Q5 | 61.21 |
2015 | ABC | Enb | PE & SE | EG | Q6 | 83.07 |
2015 | PQR | Enb | PE & SE | PE | Q1 | 34.62 |
2015 | PQR | Enb | PE & SE | PE | Q2 | 35.74 |
2015 | PQR | Enb | PE & SE | PE | Q3 | 90.00 |
2015 | PQR | Enb | PE & SE | EG | Q4 | 60.00 |
2015 | PQR | Enb | PE & SE | EG | Q5 | 80.00 |
2015 | PQR | Enb | PE & SE | EG | Q6 | 65.00 |
Complete Pivot table output:
City | Level1 | Level2 | Level3 | Level4 | Score | Rank |
ABC | Enb | 76.34 | 1 | |||
PE & SE | 76.34 | 1 | ||||
PE | 84.99 | 1 | ||||
Q1 | 84.62 | 1 | ||||
Q2 | 90.35 | 1 | ||||
Q3 | 80.00 | 2 | ||||
EG | 67.70 | 2 | ||||
Q4 | 58.82 | 2 | ||||
Q5 | 61.21 | 2 | ||||
Q6 | 83.07 | 2 | ||||
PQR | Enb | 60.89 | 1 | |||
PE & SE | 60.89 | 2 | ||||
PE | 53.45 | 2 | ||||
Q1 | 34.62 | 2 | ||||
Q2 | 35.74 | 2 | ||||
Q3 | 90.00 | 1 | ||||
EG | 68.33 | 1 | ||||
Q4 | 60.00 | 1 | ||||
Q5 | 80.00 | 1 | ||||
Q6 | 65.00 | 1 |
Final pivot table would show only 1 city at a time i.e based upon the user selection in the map Eg.if City 'ABC' selected follwoing would be the output:
City | Level1 | Level2 | Level3 | Level4 | Score | Rank |
ABC | Enb | 76.34 | 1 | |||
PE & SE | 76.34 | 1 | ||||
PE | 84.99 | 1 | ||||
Q1 | 84.62 | 1 | ||||
Q2 | 90.35 | 1 | ||||
Q3 | 80.00 | 2 | ||||
EG | 67.70 | 2 | ||||
Q4 | 58.82 | 2 | ||||
Q5 | 61.21 | 2 | ||||
Q6 | 83.07 | 2 |
I hope I was clear in explaining my query.
Thanks in advance
Hi Sunny,
Level 3 EG for City ABC is Rank 2 as the Avg Score is 67.70
Level 3 EG for City PQR is Rank 1 as the Avg Score is 68.33
I hope that clarifies.
Try this:
Pick(Dimensionality()-1,
Aggr(NODISTINCT Rank(Sum(Score)), Level1, City),
Aggr(NODISTINCT Rank(Sum(Score)), Level2, City),
Aggr(NODISTINCT Rank(Sum(Score)), Level3, City),
Aggr(NODISTINCT Rank(Sum(Score)), Level4, City))
Hi Abhishek,
That doesnot give the expected result
Dude that worked.
Thanks a ton.
My last question, as this pivot would be filter base upon the city in the map, can i use set analysis within thi cod
You can use set analysis, but what exactly are you hoping to do?
Hi Sunny,
The requirment is like this:
On the map we are showing Rank of each city.
When we click on the a specific city in the map, we are suppose to get the detailed information about the selected city(which we just created with your help).
Now as per the default functionality of qlikview, if we select any click, the data gets filtered and we would see rank as 1 every where, which does not give any insight.
Instead, the requirement is to show the rank if the selected city from the map in comparison to the other cities.
For example: if the user has selected city "ABC" from the map following is the output desired in the final pivot:
City | Level1 | Level2 | Level3 | Level4 | Score | Rank |
ABC | Enb | 76.34 | 1 | |||
PE & SE | 76.34 | 1 | ||||
PE | 84.99 | 1 | ||||
Q1 | 84.62 | 1 | ||||
Q2 | 90.35 | 1 | ||||
Q3 | 80.00 | 2 | ||||
EG | 67.70 | 2 | ||||
Q4 | 58.82 | 2 | ||||
Q5 | 61.21 | 2 | ||||
Q6 | 83.07 | 1 |
Try this:
Pick(Dimensionality()-1,
Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level1, City),
Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level2, City),
Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level3, City),
Aggr(NODISTINCT Rank(Sum({<City>}Score)), Level4, City))
Nailed it men !!!
All queries solved.
Really appreciate, your promptness and willingness to assist me on the query.
Regards,
Hitesh
I am glad I was able to help
Hi Sunny,
I rank your code on my production data and facing a challenge with Dense Rank as below:
Tried to find if we have any dense rank fucntion in qlik, but no luck.
Thanks in advance.