2 Replies Latest reply: Sep 9, 2015 3:04 PM by Yvonne Han RSS

    Label Duplicated Record and Track Price Change

    Yvonne Han

      Hi

       

      I'm tracking price change for items over month. My sample data is shown below. I need help writing a script to add two extra highlighted columns to indicate:

       

      • Multi price: if there are more than one prices for the item, label yes, otherwise, label no
      • Increase: if the price for the most recent month is the highest price, label yes, otherwise label no.

       

      Thanks!

       

       

      YearMonthItem IDPriceSellerMulti PriceIncrease
      2015JanApple $                  1.00 Farm AYesNo
      2015JanApple $                  1.00 Farm AYesNo
      2015FebApple $                  1.00 Farm AYesNo
      2015FebApple $                  1.30 Farm CYesYes
      2014DecApple $                  1.00 Farm CYesNo
      2015JanOrange $                  0.80 Farm CNoNo
      2015FebOrange $                  0.80 Farm ANoNo
      2014DecOrange $                  0.80 Farm CNoNo
      2015JanPear $                  2.00 Farm BYesNo
      2015FebPear $                  2.30 Farm AYesNo
      2014DecPear $                  2.30 Farm BYesNo
        • Re: Label Duplicated Record and Track Price Change
          Massimo Grossi

          1.png

           

           

          SET ThousandSep=',';

          SET DecimalSep='.';

          SET MoneyThousandSep='.';

          SET MoneyDecimalSep=',';

          SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

          SET TimeFormat='hh:mm:ss';

          SET DateFormat='DD/MM/YYYY';

          SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

          SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

          SET DayNames='lun;mar;mer;gio;ven;sab;dom';

           

          p:

          LOAD

            rowno() as id,

            makedate(Year, floor(Index(replace('$(MonthNames)', ';', ''), lower(Month))/3) +1) as YearMonth,

            Year,

              Month,

              [Item ID],

              replace(Price, '$', '') as Price,

              Seller

              //[Multi Price],

              //Increase

          FROM

          [https://community.qlik.com/thread/179712]

          (html, codepage is 1252, embedded labels, table is @1);

           

          left join (p)

          load

            [Item ID],

            if(count(distinct Price)>1, 'Yes', 'No') as MultiPrice,

            max(Price) as MaxPrice,

            date(max(YearMonth)) as MaxYearMonth

          resident p group by [Item ID];

           

          left join (p)

          load

            [Item ID],

            count(if(Price=MaxPrice, Price)) as CountMaxPrice

          resident p group by [Item ID];

           

          left join (p)

          load

            id,

            if(MaxYearMonth=YearMonth and MaxPrice=Price and CountMaxPrice=1, 'Yes', 'No') as Increase

          Resident p;

           

          DROP field CountMaxPrice, MaxYearMonth, MaxPrice, YearMonth;