topic Re: Date between dates in QlikView

Date between dates

andrefpc — Wed, 19 Feb 2014 18:02:59 GMT

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.

Re: Date between dates

Gysbert_Wassenaar — Wed, 19 Feb 2014 18:06:56 GMT

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

Re: Date between dates

andrefpc — Wed, 19 Feb 2014 18:10:40 GMT

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.

Re: Date between dates

swuehl — Wed, 19 Feb 2014 18:36:30 GMT

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.

Re: Date between dates

andrefpc — Wed, 19 Feb 2014 19:06:33 GMT

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