Rank() Function Issue

[#1]

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)

[#2]

Do any have any idea how to do that

[jagan]

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.

[#4]

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