Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
i want to get the citywise rank of the MEMBER according to his total. for example below is the sample table.
MEMBER | CITY | TOTAL |
A | MUMBAI | 100 |
A | PUNE | 50 |
A | DELHI | 200 |
B | DELHI | 100 |
B | PUNE | 250 |
B | MUMBAI | 200 |
B | KOLKATA | 100 |
C | MUMBAI | 50 |
C | DELHI | 500 |
Now when i select Member 'A' i want to get CITY wise TOTAL along with the rank compare to other MEMBERS like
CITY | A-TOTAL | RANK |
MUMBAI | 100 | 2 |
PUNE | 50 | 2 |
DELHI | 200 | 2 |
KOLKATA | 0 | 0 |
Kindly note this is just for a sample purpose. the actual records have more dimensions (YEAR,MONTH,DATE,etc)
Do any have any idea how to do that
Hi,
If you want static ranking then calculate in script itself it would very easier to handle. Please try below script
Temp:
LOAD
*
INLINE [
MEMBER, CITY, TOTAL
A, MUMBAI, 100
A, PUNE, 50
A, DELHI, 200
B, DELHI, 100
B, PUNE, 250
B, MUMBAI, 200
B, KOLKATA, 100
C, MUMBAI, 50
C DELHI 500];
LOAD
*,
If(Peek(CITY) <> CITY, 1, Peek(Rank) + 1) AS Rank
RESIDENT Temp
ORDER BY CITY, TOTAL, MEMBER;
Regards,
Jagan.
Hi Jagan,
i agree with you but original data have have lots of dimensions its not just MEMBER,CITY,TURNOVER, there are YEAR, MONTH, MEMBER, TRADER,CLIENT, VENDOR etc (many more) for that i needs first to make a new resident table with original table then do this on script level.
one more things . in example i do city wise, TURNOVER Rank(), same required to STATEWISE, then may also required to so in count() base etc etc.
doing this may its resource hungry and take some processes time but provide flexibility to implement easily without changing the DATA MODEL, because we do lots of Ad-hoc bases reporting also.
Thanks & Regards,
Jitendra K Vishwakarma