Dense Rank in Pivot Table for each Category - Qliksense

[Lakshmanan]

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.

[Chanty4u]

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 

    S

ubject

;

DROP TABLE RankData;

[Kushal_Chawda · Accepted solution]

@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;