2 Replies Latest reply: Sep 15, 2016 3:15 AM by Alexander Müller RSS

    IntervalMatch on with 3 keys

    Alexander Müller

      Hello,

       

      i have a problem with Intervalmatch. I have multiple keys to get a price for a product.


      Example:


      LOAD * INLINE

          

           Product, Date, Weight 

           1,    15/01/2010, 750

           1,    02/01/2010, 1500

           2,    01/04/2010, 857

      ];  


      FactsTable:
      LOAD * INLINE

           Product, Price, Start, End, Weight_from, Weight_to 
           1, 500, 01/01/2010, 31/01/2010, 0, 1000 
           1, 800, 01/01/2010, 31/01/2010, 1001, 2000
           2, 1000, 01/01/2010, 31/12/2010, 0, 1500

           2, 1100, 01/01/2011, 31/12/2011, 0, 1500

      ];  

       

      Match:

      IntervalMatch (Date, Product) load Start, End, Product

       

       

      I only have a problem with matching the Weight to the Product.

       

      I hope you can help me!

        • Re: IntervalMatch on with 3 keys
          Stefan Wühl

          Maybe using something like

           

           

           

          Set DateFormat = 'DD/MM/YYYY';

           

          Table1:

          LOAD * INLINE

             

               Product, Date, Weight

               1,    15/01/2010, 750

               1,    02/01/2010, 1500

               2,    01/04/2010, 857

          ];  

           

          FactsTable:

          JOIN

          LOAD * INLINE

               Product, Price, Start, End, Weight_from, Weight_to 

               1, 500, 01/01/2010, 31/01/2010, 0, 1000 

               1, 800, 01/01/2010, 31/01/2010, 1001, 2000

               2, 1000, 01/01/2010, 31/12/2010, 0, 1500

               2, 1100, 01/01/2011, 31/12/2011, 0, 1500

          ]; 

           

          RESULT:

          LOAD Product, Date, Weight, Price

          RESIDENT Table1

          WHERE Date <= End and Date >= Start and Weight <= Weight_to and Weight >= Weight_from;

           

          DROP Table Table1;