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: 
Valerie
Contributor III
Contributor III

Rank based on multiple conditions

Hi all

Sample Data:

CarCustomerlatest_pricelatest_timeResult needed
A1001                  102,0002019-10-18 13:03:05.0000001
A1002                    82,0002019-10-18 14:32:19.0000002
A10010                    81,0002019-10-18 14:05:17.0000004
A1005                    81,0002019-10-18 13:47:44.0000003
A1008                    70,0002019-10-18 14:56:32.0000006
A1004                    70,0002019-10-18 14:56:31.0000005
A1003                    67,0002019-10-18 13:04:23.0000007

 

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

 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
sunny_talwar

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
Contributor III
Contributor III
Author

Thanks this works. 

Appreciate all your help and respond.