topic Re: Dense Rank in Pivot Table for each Category - Qliksense in App Development

Dense Rank in Pivot Table for each Category - Qliksense

Lakshmanan — Tue, 02 May 2023 13:51:29 GMT

HI ,
i am trying to do dense rank on pivot table by each category .

Data:

	English	Math	Science
Candidate1	10	30	20
Candidate2	40	NA	20
Candidate3	30	10	40
Candidate4	30	20	40
Candidate5	20	20	NA

Excepted Output :

	English	Math	Science
Candidate1	1	3	1
Candidate2	4		1
Candidate3	3	1	2
Candidate4	3	2	2
Candidate5	2	2

i have tried

Aggr(Rank(Sum(marks),4),Candidate,Subject)

but its not giving the dense rank . I searched before posting but did not get the info .

Thanks in Advance.

Re: Dense Rank in Pivot Table for each Category - Qliksense

Chanty4u — Wed, 03 May 2023 06:30:54 GMT

Try this

Data:

LOAD * INLINE [

Candidate, English, Math, Science

Candidate1, 10, 30, 20

Candidate2, 40, NA, 20

Candidate3, 30, 10, 40

Candidate4, 30, 20, 40

Candidate5, 20, 20, NA

];

RankData:

LOAD

Candidate,

Subject,

DenseRank(Sum(Marks)) AS Rank

RESIDENT

Data

GROUP BY

Candidate,

Subject;

PivotTable:

LOAD

Candidate,

English,

Math,

Science

RESIDENT

RankData

PIVOT

ubject

;

DROP TABLE RankData;

Re: Dense Rank in Pivot Table for each Category - Qliksense

Kushal_Chawda — Wed, 03 May 2023 11:14:14 GMT

@Lakshmanan I don't think there is inbuilt function in Qlik for this kind of ranking. Even if there is a other way it will be complex to achieve it on frond end. One solution will be assign dense rank via load script.

Data: Load *, if(len(Trim(Value))=0 or Value=0,100000000000,Value) as New_Value; Load * Inline [ Candidate,Category,Value Candidate1,Category1,10 Candidate1,Category2,30 Candidate1,Category3,20 Candidate2,Category1,40 Candidate2,Category2, Candidate2,Category3,20 Candidate3,Category1,30 Candidate3,Category2,10 Candidate3,Category3,40 Candidate4,Category1,30 Candidate4,Category2,20 Candidate4,Category3,40 Candidate5,Category1,20 Candidate5,Category2,20 Candidate5,Category3, ]; Final: NoConcatenate Load *, if(Category<>Previous(Category) ,1, if(New_Value=Previous(New_Value),Peek('Dense_Rank'), if(New_Value<>100000000000,RangeSum(1,Peek('Dense_Rank'))))) as Dense_Rank Resident Data Order by Category,New_Value; Drop Field New_Value; Drop Table Data;

I have used above data based on your example. If you have more dimensions in your data where Value is not unique on Candidate & Category, you first need to aggregate your Data then apply dense_rank logic

Data: LOAD * FROM Source; Grouping: LOAD Candidate, Category, sum(Value) as Value resident Data group by Candidate,Category; left join(Data) Load *, if(Category<>Previous(Category) ,1, if(Value=Previous(Value),Peek('Dense_Rank'), RangeSum(1,Peek('Dense_Rank')))) as Dense_Rank Resident Grouping where Value>0 Order by Category,Value; drop table Grouping;