Rank based on multiple conditions

[Valerie]

Hi all

Sample Data:

Car	Customer	latest_price	latest_time	Result needed
A	1001	102,000	2019-10-18 13:03:05.000000	1
A	1002	82,000	2019-10-18 14:32:19.000000	2
A	10010	81,000	2019-10-18 14:05:17.000000	4
A	1005	81,000	2019-10-18 13:47:44.000000	3
A	1008	70,000	2019-10-18 14:56:32.000000	6
A	1004	70,000	2019-10-18 14:56:31.000000	5
A	1003	67,000	2019-10-18 13:04:23.000000	7

Result column is the rank that I required.

Conditions is as below:

First, find latest price per customer per car. So, the data before this, will have multiple price for 1 customer 1 car. I use firstsortedvalue function to get the latest price of customer by car.

Then, what I need help with is here, I need to rank based on the data in the table above, we will take the highest price to rank but if price is the same for two customers (such as customer 1102 and 10010), we will then check the time first customer that make the booking. I'm stuck with this part now sure how to do it. This required to be done in QlikSense script.

Thanks

[JordyWegman]

Hi Valerie,

Try this:

Table:
Load
  *,
  IF(RowNo() = 1,
      1,
      Peek(Result) + 1) as Result
Load
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by latest_price desc, latest_time desc;

It will first sort based on the price, then the time.

Jordy

Climber

[sunny_talwar · Accepted solution]

I think the sorting should be ascending for latest_time as the person you booked first is ranked higher.

Order by latest_price desc, latest_time;

Also, if the rank doesn't restart, this can work too

Table:
Load RowNo() as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by latest_price desc, latest_time;

if it needs to restart for each car, then may be this

Table:
Load If(Car = Previous(Car), RangeSum(Peek('Result'), 1), 1) as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by Car, latest_price desc, latest_time;

[Valerie]

Thanks this works. 

Appreciate all your help and respond.