topic Re: Rank() Function Issue in QlikView

Rank() Function Issue

Thu, 25 Jun 2015 06:21:11 GMT

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)

Re: Rank() Function Issue

Thu, 25 Jun 2015 08:59:13 GMT

Do any have any idea how to do that

Re: Rank() Function Issue

jagan — Fri, 26 Jun 2015 10:52:55 GMT

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.

Re: Rank() Function Issue

Fri, 26 Jun 2015 11:16:21 GMT

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