Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nsnybs21qv
New Contributor II

Label Duplicated Record and Track Price Change

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
Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Label Duplicated Record and Track Price Change

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;

2 Replies
MVP
MVP

Re: Label Duplicated Record and Track Price Change

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;

nsnybs21qv
New Contributor II

Re: Label Duplicated Record and Track Price Change

Thank you very much!

Community Browser