topic Re: Dinamic pricelist in QlikView

Dinamic pricelist

gidon500 — Fri, 11 Sep 2015 09:29:40 GMT

i want to get a price to a PART according to the date of transaction

enclosed is an excel file with 2 sheets

one is transaction , PART,DATE,QTY

second is PL PART,VALIDDATE , PRICE

i need to find the PRICE for each transaction

according to the PART AND VALIDDATE

thanks

gidon

Re: Dinamic pricelist

MK_QSL — Fri, 11 Sep 2015 09:51:14 GMT

TempPrice:
Load * Inline
[
  PART, VALIDDATE, PRICE
  100, 01/01/2015, 100
  100, 15/02/2015, 150
  200, 01/01/2015, 70
  200, 04/03/2015, 60
  300, 05/02/2015, 80
];


NoConcatenate
Price:
Load 
  PART,
  VALIDDATE as ValidFrom,
  Date(IF(PART = PREVIOUS(PART), Previous(VALIDDATE)-1, Today())) as ValidTo,
  PRICE
Resident TempPrice
Order By PART, VALIDDATE DESC;


Drop Table TempPrice;


Trans:
Load * Inline
[
  PART, TRANSDATE, QTY
  100, 05/01/2015, 5
  200, 15/02/2015, 3
  300, 17/02/2015, 4
  100, 16/02/2015, 6
  200, 10/04/2015, 3
];


Join (Trans)
IntervalMatch(TRANSDATE,PART)
Load ValidFrom, ValidTo, PART Resident Price;


Left Join (Trans) Load * Resident Price;


Drop Table Price;

Re: Dinamic pricelist

gidon500 — Fri, 11 Sep 2015 10:21:54 GMT

Hi Manish

thanks it works great and easy

gidon