topic Label Duplicated Record and Track Price Change in QlikView

Label Duplicated Record and Track Price Change

Anonymous — Tue, 08 Sep 2015 21:26:00 GMT

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

Re: Label Duplicated Record and Track Price Change

maxgro — Tue, 08 Sep 2015 22:04:50 GMT

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';

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;

Re: Label Duplicated Record and Track Price Change

Anonymous — Wed, 09 Sep 2015 19:04:56 GMT

Thank you very much!