topic Conditional Ranking in Script in QlikView

Conditional Ranking in Script

mbespartochnyy — Thu, 27 Jul 2017 14:49:09 GMT

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?

Thanks in advance,

Mikhail B.

Re: Conditional Ranking in Script

sunny_talwar — Thu, 27 Jul 2017 15:05:16 GMT

Try this

RankingPrep:

LOAD Market,

NormName,

Spend

FROM

[Ranking.xlsx]

(ooxml, embedded labels, table is Sheet1);

Ranking:

LOAD Market,

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

mbespartochnyy — Thu, 27 Jul 2017 15:40:19 GMT

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:

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).
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.
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

sunny_talwar — Thu, 27 Jul 2017 15:44:28 GMT

Here's how I read the logic:

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).

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.

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)