Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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:
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