topic Re: Rank function in straight table for all duplicate rows in QlikView

Rank function in straight table for all duplicate rows

Wed, 25 Nov 2020 16:16:04 GMT

@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

Re: Rank function in straight table for all duplicate rows

Fri, 03 Mar 2017 18:12:43 GMT

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;

Re: Rank function in straight table for all duplicate rows

sunny_talwar — Fri, 03 Mar 2017 18:14:11 GMT

May be like this

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

Re: Rank function in straight table for all duplicate rows

Fri, 03 Mar 2017 22:26:53 GMT

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

Re: Rank function in straight table for all duplicate rows

Fri, 03 Mar 2017 22:27:39 GMT

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

Re: Rank function in straight table for all duplicate rows

Fri, 03 Mar 2017 22:47:38 GMT

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

Re: Rank function in straight table for all duplicate rows

ahaahaaha — Sat, 04 Mar 2017 04:46:15 GMT

Hi Deepika,

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

Regards,

Andrey

Re: Rank function in straight table for all duplicate rows

Fri, 07 Apr 2017 15:42:19 GMT

Hi,

Can somebody help

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

Thanks in advance