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

    Conditional Ranking in Script

    Mikhail Bespartochnyy

      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.

        • Re: Conditional Ranking in Script
          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;

            • Re: Conditional Ranking in Script
              Mikhail Bespartochnyy

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