Rank function in straight table for all duplicate rows

[#1]

@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

[#2]

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;

[sunny_talwar]

May be like this

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

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]

Hi Deepika,

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

Regards,

Andrey

[#8]

Hi,

Can somebody help

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

Thanks in advance