Label Duplicated Record and Track Price Change

[Anonymous]

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!

Year	Month	Item ID	Price	Seller	Multi Price	Increase
2015	Jan	Apple	$                  1.00	Farm A	Yes	No
2015	Jan	Apple	$                  1.00	Farm A	Yes	No
2015	Feb	Apple	$                  1.00	Farm A	Yes	No
2015	Feb	Apple	$                  1.30	Farm C	Yes	Yes
2014	Dec	Apple	$                  1.00	Farm C	Yes	No
2015	Jan	Orange	$                  0.80	Farm C	No	No
2015	Feb	Orange	$                  0.80	Farm A	No	No
2014	Dec	Orange	$                  0.80	Farm C	No	No
2015	Jan	Pear	$                  2.00	Farm B	Yes	No
2015	Feb	Pear	$                  2.30	Farm A	Yes	No
2014	Dec	Pear	$                  2.30	Farm B	Yes	No

[maxgro · Accepted solution]

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;

[Anonymous]

Thank you very much!