Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharathkamath28
Contributor III
Contributor III

Rank based on two columns in Qliksense

Hi Qlikexperts,

How to get the below output Rank in Qliksense

bharathkamath28_0-1608203289785.png

 

 

 

Tried

Load

autonumber(Value,Name) as Rank

Load Table;

order by Name,Number asc

 

But i am not getting the desired output

 

Thanks,

 

Labels (1)
4 Replies
Taoufiq_Zarra

@bharathkamath28  maye be like this :

Data:
LOAD *,if(trim(len(Value))=0,0,Value) as TmpValue INLINE [
    ID, Name, Value
    1, A, 
    2, A, 12
    3, A, 13
    4, A, 13
    5, B, 10
    6, C, 15
    7, C, 15
    8, D, 17
    9, D, 17
    10, D, 18
    11, D, 18
    12, D, 18
    13, D, 18
    14, E, 
    15, F, 25
    16, F, 
];

output:
noconcatenate

load ID, Name, Value,if(peek(Name)=Name and peek(Value)<>Value,peek(Rank)+1,if(peek(Name)=Name and peek(Value)=Value,peek(Rank),1)) as Rank;
load * resident Data order by Name,TmpValue;

drop table Data;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
bharathkamath28
Contributor III
Contributor III
Author

@Taoufiq_Zarra 

Thanks for the solution.

there's slightly some changes in the output.

below is the one

bharathkamath28_0-1608203386873.png

How can we achieve this?

 

 

Taoufiq_Zarra

@bharathkamath28  ok

Data:
LOAD *,if(trim(len(Value))=0,0,Value) as TmpValue INLINE [
    ID, Name, Value
    1, A, 
    2, A, 12
    3, A, 13
    4, A, 13
    5, B, 10
    6, C, 15
    7, C, 15
    8, D, 17
    9, D, 17
    10, D, 18
    11, D, 18
    12, D, 18
    13, D, 18
    14, E, 
    15, F, 25
    16, F, 
];

output:
noconcatenate

load ID, Name, Value,if(peek(Name)=Name and peek(Value)<>Value,peek(Rank)+1,if(peek(Name)=Name and peek(Value)=Value,peek(Rank),0)) as Rank;
load * resident Data order by Name,TmpValue;

drop table Data;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
bharathkamath28
Contributor III
Contributor III
Author

@Taoufiq_Zarra 

This solution is not working for me

I have a huge data. 

Do we have another other logic like autonumber