Dinamic pricelist

[gidon500]

hi

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

[MK_QSL · Accepted solution]

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;

[gidon500]

Hi Manish

thanks it works great and easy

gidon