RANKING on Load Script

[#1]

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

[MK_QSL]

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;

[miguelbraga]

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

[sunny_talwar]

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:

[#5]

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