3 Replies Latest reply: Jul 27, 2017 11:44 AM by Sunny Talwar

# Conditional Ranking in Script

Hello everyone,

I'm facing a challenge with ranking hotels by spend. Attached is a sample data that I'm working on and the best solution that I came up with. I'm using AutoNumber() function to assign increasing values for each hotel in a market in a table sorted by spend in descending order. The issue that I'm running into is that if there are two hotels in the same market, AutoNumber assigns continuous numbering instead of skipping step for those instances where two hotels have same spend.

Example is below:

 Market Hotel Name Spend Expression Rank Script AutoNumber Desired Rank NAMER - UNITED STATES - FL - TAMPA HMPTN STE TAMPA YBOR CTY DNTWN 270 16-17 16 16 NAMER - UNITED STATES - FL - TAMPA HOLIDAY INN EXP STES ROCKT PT 270 16-17 17 16

I'd like both Hampton and Holiday Inn to be ranked 16, but both script and Rank expression function assign 16 and 17. Is there a way to assign same ranking if spend is the same?

Mikhail B.

• ###### Re: Conditional Ranking in Script

Try this

RankingPrep:

NormName,

Spend

FROM

[Ranking.xlsx]

(ooxml, embedded labels, table is Sheet1);

Ranking:

NormName,

Spend,

If(Market = Previous(Market), If(Spend = Previous(Spend), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank,

AutoNumber(NormName, Market) as SpendRank

Resident RankingPrep

Order By Market, Spend desc;

DROP Table RankingPrep;

• ###### Re: Conditional Ranking in Script

I had to chew on that for a bit to wrap my head around what happened, but I think I got it. I had no idea that Peek function can can be used to reference data that is not yet created and that it references previous row if row_no argument is not specified.

Here's how I read the logic:

1. Check, each line in a table if Market is the same, if it's not, you start with a rank of 1 because it's either the very first row of already correctly sorted table or it's a new Market (again in a sorted table).
2. If Market on a line is the same as Market on previous line, you check if spend is not the same. If it spend is not the same, you use RangeSum function to add rank from previous row (using Peek() function) to a 1.
3. If Market on a line is the same as Market on Previous line, and spend is same as spend on previous line, you select (using Peek() function) the rank from previous row.

Thanks, Sunny!

Mikhail B.

• ###### Re: Conditional Ranking in Script

Here's how I read the logic:

1. Check, each line in a table if Market is the same, if it's not, you start with a rank of 1 because it's either the very first row of already correctly sorted table or it's a new Market (again in a sorted table).
2. If Market on a line is the same as Market on previous line, you check if spend is not the same. If it spend is not the same, you use RangeSum function to add rank from previous row (using Peek() function) to a 1.
3. If Market on a line is the same as Market on Previous line, and spend is same as spend on previous line, you select (using Peek() function) the rank from previous row.

This is exactly what it is doing. and Peek/Previous approach is much better than using AutoNumber because AutoNumber drastically increase the reload time (don't know why)