1 Reply Latest reply: Feb 4, 2014 6:28 PM by Ryan King-Thornburg RSS

    Intervalls - Calculation

    Stephan Freitag

      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!

        • Re: Intervalls - Calculation
          Ryan King-Thornburg

          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.