Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank function in straight table for all duplicate rows

@Hello All,

I am just started working with Qlikview and doing some analysis. I have some data like:

Column:A    Column:B  Count(derived)  Rank(?)

  A1            24                   2                     2

A1               21                  2                     2

A2               32                  3                   1

A2                  45                  3                   1

A2                    56                3                    1

I want to derive Rank Column which gives rank to the column A with maximum number of contents in Column B.

I tried rank function but it displays only in one row but I require the ranks to display on the all the rows per columnA content.

I hope my question is clear. Is there any way to do it or only using rank function.

Please Please help!

Thanks in advance,

Deepika

1 Solution

Accepted Solutions
Not applicable
Author

Hi Sunny,

Thanks for giving me a start to the logic. I just added more arguments  to rank function and got the results correctly.

= aggr(nodistinct rank(aggr(count( distinct ColB),ColA),1,1),ColA).

Thanks a lot!

Cheers,

Deepika

View solution in original post

7 Replies
Not applicable
Author

Test:

LOAD * INLINE [

    ColA, ColB

    A1, 24

    A1, 21

    A2, 32

    A2, 45

    A2, 56

];

Test2:

Load max(ColB) as M,ColA Resident Test Group by ColA ;

Left join (Test)

load ColA, RecNo() as Rank Resident Test2 Order by M desc;

drop table Test2;

col.PNG

sunny_talwar

May be like this

Aggr(NODISTINCT Rank(Count(derived)), ColumnA)

Not applicable
Author

Hi Ravindra,

Thanks for your reply. But I want to do this in set analysis or directly in straight table expression.

Could you please suggest an alternative to load script that you showed .

Thanks

Not applicable
Author

Hi Sunny, Thanks for your reply. But this didnt work.

Not applicable
Author

Hi Sunny,

Thanks for giving me a start to the logic. I just added more arguments  to rank function and got the results correctly.

= aggr(nodistinct rank(aggr(count( distinct ColB),ColA),1,1),ColA).

Thanks a lot!

Cheers,

Deepika

ahaahaaha
Partner - Master
Partner - Master

Hi Deepika,

There is a proposal to note as correct answer Sunny for the idea, thereby closing this post.

Regards,

Andrey

Not applicable
Author

Hi,

Can somebody help

I need to find all the fields which have missing values while loading from csv file.

Thanks in advance