Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
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;
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;
Thank you very much!