Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mbespartochnyy
Creator II
Creator II

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:

      

MarketHotel NameSpendExpression RankScript AutoNumberDesired Rank
NAMER - UNITED STATES - FL - TAMPAHMPTN STE TAMPA YBOR CTY DNTWN27016-171616
NAMER - UNITED STATES - FL - TAMPAHOLIDAY INN EXP STES ROCKT PT27016-171716

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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;

mbespartochnyy
Creator II
Creator II
Author

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.

sunny_talwar

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)