4 Replies Latest reply: Mar 16, 2018 5:32 PM by Luiz De Paula RSS

    Condition based on date range

    Luiz De Paula

      Hello experts,

      I have a challenge that I thought it was going to be easy, but I definitely was not able to come up with the solution.

      There was a price change for a material where the initial price was $16.8 per case with price valid from 1/1/2011 and good until 3/31/2018. With the price change, starting on 4/1/2018, each case will now cost $20. So, I need to make a date comparison to the price date range.

       

      Example:

      If "Forecast Month" > "Valid from" and "Forecast Month" < "Valid to", than "Price per case".

      So, following this logic, based on the data below, the results should be like below:

      "Forecast Month" = 3/1/2018, than "Price per case" = 16.8

      "Forecast Month" = 4/1/2018, than "Price per case" = 20

       

      Note: I attached an Excel file with this data below to make it easier if you want to try it.

       

      Thanks in advance,

       

      LD

        • Re: Condition based on date range
          Jerry Jackson

          Have you considered the IntervalMatch function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/IntervalMatch.htm) in you load script?

           

          The following has not been tested:

           

          Data:

          load * inline [

          Material,Cases,ForecastMonth

          300610,65719,04/01/2018

          300610,57871,0301/2018

          ];

           

          Price:

          load * inline [

          From,To,Price,Material

          01/01/2011,03/31/2018,16.8,300610

          04/01/2018,12,31,999,20,30060

          ];

           

          Inner join IntervalMatch(ForecastMonth,Material)

          load From,To,Material

          Resident Price;

            • Re: Condition based on date range
              Luiz De Paula

              Hi Jerry,

              First, thank you for the quick response and trying to help me figure this out.

              However, I need a more dynamic solution. Maybe I have given a simple example.

               

              I have a logic in Qlik where I pull cases and List Price per case from the system, and calculate the Dollar value, by multiplying them. However, the List Price might change once in a while, for several products, like hundreds. So, I need a logic based on the data range of the List Price, so that Qlik uses the List Price for that specific date range, Valid from and Valid to.

               

              Example:

              If "Forecast Month" (First day of the month) falls into the date range of the List Price, give me the List Price for that date range. I could pull the most current List Price, however we need to keep the old ones for historical transactions.

               

              Let me know if you have other ideas,

               

              Thanks

               

              LD

                • Re: Condition based on date range
                  Jerry Jackson

                  You would populate the data for the IntervalMatch from your database, not an inline load as in my example.  You would have the historical prices still attached to records where the from/to date range was appropriate.

                    • Re: Condition based on date range
                      Luiz De Paula

                      Jerry,

                       

                      Thanks for your help. The IntervalMarch did the work, exactly like you mentioned before. I used a Left Join after the load, in order to create a table I could save a use it for future calculation in the script. The final script it as follows:

                       

                      LIST_PRICE:

                      LOAD

                          MATNR,

                          LIST_PRICE,

                          Valid_To,

                          Valid_From

                      FROM LIST_PRICE_USD.qvd;

                       

                      FORECAST:

                      LOAD

                          MATNR,

                          Forecast,

                          fDate,

                      FROM FORECAST.qvd;

                       

                      Inner join

                      IntervalMatch (fDate,[MATNR]) Load Valid_From, Valid_To,MATNR Resident LIST_PRICE ;

                       

                      Left Join

                      Load

                          MATNR,

                          LIST_PRICE,

                          Valid_To,

                          Valid_From

                      Resident LIST_PRICE;

                      Drop Table LIST_PRICE;

                       

                      Thanks again!

                       

                      LD