Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

RANKING on Load Script

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

DATEMEMBERCLIENTCITYSTATETURNOVER
12/8/2015RAMESHAAMUMBAIMAHARASHTRA100
12/8/2015RAMESHBBMUMBAIMAHARASHTRA100
12/8/2015YOGESHXXMUMBAIMAHARASHTRA200
12/8/2015YOGESHZZMUMBAIMAHARASHTRA100
12/8/2015YOGESHYYDELHINEW DELHI300
12/8/2015RAMESHCCDELHINEW DELHI400

OUTPUT

STATEMEMBERTOTAL TURNOVERRANK
MAHARASHTRAYOGESH3001
MAHARASHTRARAMESH2002
NEW DELHIRAMESH4001
NEW DELHIYOGESH3002

SAMPLE TABLE IS ATTACHED FOR WORKING PURPOSE

4 Replies
MK_QSL
MVP
MVP

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
Partner - Specialist III
Partner - Specialist III

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:

Capture.PNG

Not applicable
Author

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