1 Reply Latest reply: Apr 11, 2018 3:23 AM by Ihor Ostapchuk RSS

    Get Price from price list changes file on the basis of order date

    eeacrw cewdbr

      Hi!

       

      I have a Table 1 with price list changes for a commodity:

       

      Date of change
      Commodity ID
      New Price
      2017 Jan 01132$10.5
      2017 Jan 01150$20
      2017 March 04132$12
      2017 July 10132$15
      2017 Aug 01150$25

      (for about 1000 commodities and several years)

       

      And I have a Table 2 with orders (but w/o prices), and I need to get correct price from Table 1 on the basis of order date so it should look like (marked red):

       

      Order Date
      Commodity ID
      Quantity
      Unit price
      2017 Jan 0313210$10.5
      2017 Jan 3013210$10.5
      2017 Mar 0515010$20
      2017 Aug 1513210$15
      2017 Aug 2015010$25

      Order date should be compared to price change dates, and the closest from the past should be taken (or the same date if order date equals date of change).

       

       

      How it can be done using Qlik?

       

      Thanks!

        • Re: Get Price from price list changes file on the basis of order date
          Ihor Ostapchuk

          use IntervalMatch.

           

           

           

          LET vCurrentDate = Today();

          // ======================================================================

          price:

          LOAD

              "Date of change",

              "Commodity ID",

              "New Price"

          FROM [lib://path_DB (rbcgrp_ostapchuk)/test\price_order.xlsx]

          (ooxml, embedded labels, table is [Table 1]);

           

           

          // ======================================================================

          order:

          LOAD

              "Order Date",

              "Commodity ID",

              Quantity

          FROM [lib://path_DB (rbcgrp_ostapchuk)/test\price_order.xlsx]

          (ooxml, embedded labels, table is [Table 2]);

           

           

          // ======================================================================

          Price_ID:

          NoConcatenate

          Load

          "Commodity ID",

              "Date of change" as DateStart,

              date(if(Previous("Commodity ID")="Commodity ID",peek('DateStart')-1,'$(vCurrentDate)')) as DateEnd,

              "New Price"

          Resident price

          order By

          "Commodity ID",

              "Date of change" desc;

           

           

          Drop Tables price;

             

             

          // ======================================================================

          Inner Join IntervalMatch("Order Date","Commodity ID")

          Load

          DateStart,

              DateEnd,

              "Commodity ID"

          Resident Price_ID;