topic Re: How to include equal rank rows in QlikView

How to include equal rank rows

Anonymous — Wed, 17 Aug 2016 23:39:00 GMT

I have a pivot table that's pulling top 5 vendors. it is working except when there is a tie. the middle table is a pivot table which is only pulling up to vendor 5. I want it to pull vendors 1,2,3,4,5 and 6 (First Table has simple count function where its bringing all vendors). the logic is: if there is a tie, it should ignore th rank.

I am using sum(aggr(if((rank(count({ < fieldA = {'1'}>} ID))) <=5,count({< FieldA = {1}>} ID)),Group, Vendor)) in the expression but it is not working. Can you please suggest. I have also included load inline statement below if you would like to reload.

Thanks again

LOAD * INLINE [
Group, Vendor, ID, ADD, FieldA
A, 1, A1, 1, 1
A, 1, A1, 2, 1
A, 1, A1, 3, 1
A, 1, A1, 4, 1
A, 1, A1, 5, 1
A, 1, A2, 6, 1
A, 1, A3, 7, 1
A, 1, A4, 8, 1
A, 1, A5, 9, 1
A, 1, A6, 10, 1
A, 1, A7, 11, 1
A, 1, A8, 12, 1
A, 1, A9, 13, 1
A, 2, A11, 15, 1
A, 2, A12, 16, 1
A, 2, A13, 17, 1
A, 2, A72, 18, 1
A, 2, A14, 18, 1
A, 3, A73, 19, 1
A, 3, A15, 19, 1
A, 3, A16, 20, 1
A, 3, A17, 21, 1
A, 3, A18, 22, 1
A, 4, A19, 23, 1
A, 4, A20, 24, 1
A, 4, A21, 25, 1
A, 4, A22, 26, 1
A, 5, A60, 27, 1
A, 5, A23, 27, 1
A, 5, A24, 28, 1
A, 5, A25, 29, 1
A, 6, A73, 30, 1
A, 6, A26, 30, 1
A, 6, A27, 31, 1
A, DD, A29, 31, 1
A, DD, A30, 31, 1
A, CC, A28, 31, 1
A, EF, A31, 31, 1
A, GH, A32, 31, 1
B, 12, A34, 31, 1
B, 1, A1, 1, 1
B, 1, A1, 2, 1
B, 1, A1, 3, 1
B, 1, A1, 4, 1
B, 2, A1, 5, 1
B, 2, A2, 6, 1
B, 2, A3, 7, 1
B, 3, A4, 8, 1
B, 3, A5, 9, 1
B, 3, A15, 19, 1
B, 3, A16, 20, 1
B, 3, A17, 21, 1
B, 6, A1, 1, 1
B, 6, A1, 2, 1
B, 6, A1, 3, 1
B, 6, A1, 4, 1
B, 6, A1, 5, 1
B, 6, A2, 6, 1
B, 6, A3, 7, 1
B, 6, A4, 8, 1
B, CC, A28, 31, 1
B, DD, A29, 31, 1
B, DD, A30, 31, 1
B, EF, A31, 31, 1
B, GH, A32, 31, 1
C, CC, 6, 32, 1
C, CC, 7, 32, 1
C, CC, 8, 33, 1
C, DD, 4, 34, 1
C, DD, 5, 35, 1
C, 1, 8, 36, 1
C, 1, 9, 37, 1
C, 2, 4, 38, 1
C, 3, 5, 39, 1
D, 4, 8, 40, 1
D, 5, 9, 41, 1
];

Re: How to include equal rank rows

sunny_talwar — Thu, 18 Aug 2016 00:35:42 GMT

There are two ties in your table, 2 5s and 2 4s, would you want to bring 1, 2, 3, 4, 5, 6, 7 in this case?

Re: How to include equal rank rows

Anonymous — Thu, 18 Aug 2016 01:28:21 GMT

HI sunny,

yes there are 2 ties. when there is a tie, for eg. with vendor 2&3 has 5; vendor 4&5 has 4, i want the function to treat rank as shown by Position colm below:

Vendor	Count	Position
1	13	1
2	5	2
3	5	2
4	4	3
5	4	3
6	3	4
DD	2	5

yes u are right, based on this, it should show 1, 2, 3, 4, 5, 6 & DDin this case. Thanks much

Re: How to include equal rank rows

vishsaggi — Thu, 18 Aug 2016 02:54:57 GMT

Funny thing is I am not sure how it worked but try this :

= sum(aggr(if( Rank(rank(count({ < fieldA = {'1'}>} ID),3,1),4)>=4 ,count({< FieldA = {1}>} ID)),Group, Vendor))

Sunny could you look into this and explain if possible??

Re: How to include equal rank rows

Anonymous — Thu, 18 Aug 2016 02:56:18 GMT

Here is the summary on what I need:

also pls find qvw attached. Thanks in advance.

Re: How to include equal rank rows

vishsaggi — Thu, 18 Aug 2016 03:02:42 GMT

Hello John,

Did you try that expression i have posted above ?

Re: How to include equal rank rows

Anonymous — Thu, 18 Aug 2016 03:59:45 GMT

Hi Vish Thanks for your reply. yes it works in this sample. Let me try in the actual app and will post here. can you explain your logic. bcoz I need to pull top 5. not sure how your code fits the top 5 logic

Re: How to include equal rank rows

qliksus — Thu, 18 Aug 2016 05:32:33 GMT

Hi,

Maybe something like this

Exp: count({< FieldA = {1},Vendor={"=rangesum(Above(if( Above(count({< FieldA = {1}>} ID))=count({< FieldA = {1}>} ID),0, 1),0,RowNo()))"}>} ID)

Re: How to include equal rank rows

sunny_talwar — Thu, 18 Aug 2016 07:56:38 GMT

It did seem to work well for A, but how about C, I don't think it worked for C, right?

Showing just two values. I know we can resolve this in the script, but then we loose the dynamicaness of the Rank function.

Re: How to include equal rank rows

sunny_talwar — Thu, 18 Aug 2016 08:09:20 GMT

This seems like a promising approach. Just a few tweaks to make it work:

Script:

LOAD *,

AutoNumber(Group&Vendor) as Key

INLINE [

Group, Vendor, ID, ADD, FieldA

A, 1, A1, 1, 1

A, 1, A1, 2, 1

A, 1, A1, 3, 1

A, 1, A1, 4, 1

...

Expression:

Count({< FieldA = {1}, Key = {"=Aggr(RangeSum(Above(If(Above(Count({< FieldA = {1}>} ID)) = Count({< FieldA = {1}>} ID),0, 1),0,RowNo())), Group, Vendor) <= 5"}>} ID)

Output

Re: How to include equal rank rows

Anonymous — Thu, 18 Aug 2016 15:52:36 GMT

‌thank you sunny. I will try this and post it soon here

Re: How to include equal rank rows

Anonymous — Fri, 19 Aug 2016 02:36:12 GMT

HI sunny my data model is a little complex: 'Group' that rolls up based on Filename is in a diff table than 'Vendor' and ID & Field A. because of which I am not getting correct result. If it was one table then of course what you suggested was brilliant. I am still working on it, the app is pretty big. will post. Thank you so much. Kind Regards.