Announcements
cancel
Showing results 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.

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

7 Replies
Not applicable
Author

Test:

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;

MVP

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

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

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.