Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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.