topic RANKING on Load Script in QlikView

RANKING on Load Script

Mon, 28 Sep 2015 09:12:57 GMT

Dear All,

i have a table with some dimension like CITY and STATE. I want to get STATE wise rank on TURNOVER base on load.

like below :

sample

DATE	MEMBER	CLIENT	CITY	STATE	TURNOVER
12/8/2015	RAMESH	AA	MUMBAI	MAHARASHTRA	100
12/8/2015	RAMESH	BB	MUMBAI	MAHARASHTRA	100
12/8/2015	YOGESH	XX	MUMBAI	MAHARASHTRA	200
12/8/2015	YOGESH	ZZ	MUMBAI	MAHARASHTRA	100
12/8/2015	YOGESH	YY	DELHI	NEW DELHI	300
12/8/2015	RAMESH	CC	DELHI	NEW DELHI	400

OUTPUT

STATE	MEMBER	TOTAL TURNOVER	RANK
MAHARASHTRA	YOGESH	300	1
MAHARASHTRA	RAMESH	200	2
NEW DELHI	RAMESH	400	1
NEW DELHI	YOGESH	300	2

SAMPLE TABLE IS ATTACHED FOR WORKING PURPOSE

Re: RANKING on Load Script

MK_QSL — Mon, 28 Sep 2015 09:23:32 GMT

Data:
Load * Inline
[
  DATE, MEMBER, CLIENT, CITY, STATE, TURNOVER
  08/12/2015, RAMESH, AA, MUMBAI, MAHARASHTRA, 100
  08/12/2015, RAMESH, BB, MUMBAI, MAHARASHTRA, 100
  08/12/2015, YOGESH, XX, MUMBAI, MAHARASHTRA, 200
  08/12/2015, YOGESH, ZZ, MUMBAI, MAHARASHTRA, 100
  08/12/2015, YOGESH, YY, DELHI, NEW DELHI, 300
  08/12/2015, RAMESH, CC, DELHI, NEW DELHI, 400
];


Rank:
Load
  STATE,
  MEMBER,
  SUM(TURNOVER) as [TOTAL TURNOVER]
Resident Data
Group By STATE, MEMBER;


Drop Table Data;


Left Join (Rank)
Load STATE, MEMBER, [TOTAL TURNOVER], IF(STATE <> Peek(STATE), 1, RangeSum(Peek(Rank),1)) as Rank
Resident Rank
Order By STATE, [TOTAL TURNOVER] Desc;

Re: RANKING on Load Script

miguelbraga — Mon, 28 Sep 2015 09:32:24 GMT

Hi Jitendra Vishwakarma,,

Please see the attached .qvw file that I created with your solution. Hope it helped.

Some notes:

TOTAL TURNOVER 😆 sum({<STATE =>} TURNOVER)

RANK 😆 rank(sum({<STATE =>} TURNOVER))

Miguel

Re: RANKING on Load Script

sunny_talwar — Mon, 28 Sep 2015 09:42:55 GMT

You can also try this:

Table:

LOAD DATE,

MEMBER,

CLIENT,

CITY,

STATE,

TURNOVER

FROM

[SAMPLE (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Join (Table)

LOAD STATE,

MEMBER,

SUM(TURNOVER) as TOTALTURNOVER

Resident Table

Group By STATE, MEMBER;

NewTable:

LOAD *,

AutoNumber(TOTALTURNOVER, STATE) as Rank

Resident Table

Order By STATE, TOTALTURNOVER desc;

DROP Table Table;

Output in a table box object:

Re: RANKING on Load Script

Tue, 29 Sep 2015 03:54:15 GMT

Currently i am using this way only in Application. the data what i have shared is just a sample. i have around 10-15 columns and 3cr records and facing some performance issue. so think it is good to process at load time only.

Regards,

Jitendra Kumar Vishwakarma