Date between dates

[andrefpc]

Hello everyone!

My current project involves calculating a discount, for every invoice based on a price list which has a starting and ending date, the trick is there are different price lists for every product with different dates, so...what i have to do is, for the date of the invoice, check the product and the price list used to calculate the price, is this achievable with interval Match? Is there any other way around?

Thanks in advance.

[Gysbert_Wassenaar]

That sounds like intervalmatch could be useful. See this document: IntervalMatch and Slowly Changing Dimensions

[andrefpc]

Thank you Gysbert Wassenaar, I've already checked that document, I followed it and I was getting more than 54 Million rows when running the qvw, that can't be right.

EDIT:

I was using Method 2: Using a While loop creating enumerable values. I don't want to use Interval Match, except if there's no other way.

[swuehl]

Can't say anything about the number of rows you get (I think that's depending on your number of invoices, price list intervals etc.), but I think you need to look at the solution described starting on page 16 ff. (Using a while loop and applymap) to consider the product as additional key.

[andrefpc]

well...I have 828 distinct invoice dates and more than 25000 products, for every product I have 3 price lists.