Intervalls - Calculation

[#1]

Hello guys,

Im new in qlikview an i have no idea to solve the folowing try in calculation.

I have two tables. the first one with calculation results of a article. The second one with quatities of this article.

Table one

date_calc	art_no	calc_result
01.05.2013	12345	1
01.06.2013	12345	2
05.06.2013	12345	3
01.10.2013	12345	4

Table two

date_ord	ord_no	art_no	qty_ord
01.04.2013	001	12345	5
30.05.2013	002	12345	5
10.08.2013	003	12345	5
31.12.2013	004	12345	5

Now i have no idea how to get the right value of "calc_result" for orders in my calculation.

If  date_order <date_calc, so it should be the value oft the first available date oft date_calc.

If date_calc<date_order, i need the calc_result value of last avaiable calc_date in the table.

I have here the e.g for my tables:

Calculation

ord_date	ord_no	art_no	RES (calc_result * qty_ord)
01.04.2013	001	12345	1*5 = 5
30.05.2013	002	12345	1*5 = 10
10.08.2013	003	12345	3*5 = 15
31.12.2013	004	12345	4*5 =20

I hope you have any idea..

thanks!

[milkyryan]

Hi, it's a bit hard to gauge what your overall requirements are based on your example/descriptions, but I'm guessing you need to calculate using a date range based multiplier on the data in table 2?

If so, I would suggest using the intervalmatch function in your script and adding a date start/end field in table 1. This will create an intervalmatch table which will join any orders falling between given dates and finding the relevant multiplier (calc_result). I recommend searching on the forum for other examples of the intervalmatch function.