5 Replies Latest reply: Jun 22, 2015 8:10 AM by Henric Cronström RSS

    Match two table

    Paco Rios
      I need to apply these sales discounts given date sales count them . Can anyone help me?
      Discount:
      LOAD * INLINE [
      Customer, Star Date, End Date, Discount
      1, 01/01/2015, 31/12/2015, 5
      1, 01/02/2015, 31/12/2015, 4
      2, 01/01/2015, 31/01/2015, 2
      3, 01/01/2015, 31/01/2015, 5
      4, 01/06/2015, 30/06/2015, 10
      4, 01/01/2015, 31/12/2015, 5
      ]
      ;

      Sales:
      LOAD * INLINE [
      Customer, SalesDate, Amount
      1, 01/01/2015, 150
      2, 01/05/2015, 98
      3, 01/01/2015, 75
      4, 10/06/2015, 157
      1, 01/02/2015, 140
      2, 28/01/2015, 80
      3, 31/05/2015, 210
      4, 20/04/2015, 150
      ]
      ;
        • Re: Match two table
          Henric Cronström

          See IntervalMatch

           

          HIC

            • Re: Match two table
              Paco Rios

              Hi Henric,

               

              If I use this function I'm doubling my sales.


               

               

              Discount:
              LOAD * INLINE [
              Customer, Start Date, End Date, Discount
              1, 01/01/2015, 31/12/2015, 5
              1, 01/02/2015, 31/12/2015, 4
              2, 01/01/2015, 31/01/2015, 2
              3, 01/01/2015, 31/01/2015, 5
              4, 01/06/2015, 30/06/2015, 10
              4, 01/01/2015, 31/12/2015, 5
              ]
              ;

              Sales:
              LOAD * INLINE [
              Customer, SalesDate, Amount
              1, 01/01/2015, 150
              2, 01/05/2015, 98
              3, 01/01/2015, 75
              4, 10/06/2015, 157
              1, 01/02/2015, 140
              2, 28/01/2015, 80
              3, 31/05/2015, 210
              4, 20/04/2015, 150
              ]
              ;
              Left Join IntervalMatch (SalesDate,Customer)
               
              LOAD
              [Start Date],
              [End Date],
              Customer
              RESIDENT Discount;
              Left Join  LOAD * RESIDENT Discount;
               
              DROP TABLE Discount;

                • Re: Match two table
                  Paco Rios

                    • Re: Match two table
                      Henric Cronström

                      Yes you need the extended intervalmatch. See Slowly Changing Dimensions

                       

                      This script should do the trick:

                       

                      Discount:
                      LOAD *, Customer & '|' & [Star Date] & '|' & [End Date] as CustomerIntervalID
                      INLINE [
                      Customer, Star Date, End Date, Discount
                      1, 01/01/2015, 31/12/2015, 5
                      1, 01/02/2015, 31/12/2015, 4
                      2, 01/01/2015, 31/01/2015, 2
                      3, 01/01/2015, 31/01/2015, 5
                      4, 01/06/2015, 30/06/2015, 10
                      4, 01/01/2015, 31/12/2015, 5]
                      ;

                      Sales:
                      LOAD *, Customer & '|' & [SalesDate] as CustomerDateID
                      INLINE [
                      Customer, SalesDate, Amount
                      1, 01/01/2015, 150
                      2, 01/05/2015, 98
                      3, 01/01/2015, 75
                      4, 10/06/2015, 157
                      1, 01/02/2015, 140
                      2, 28/01/2015, 80
                      3, 31/05/2015, 210
                      4, 20/04/2015, 150]
                      ;

                      tmpIntervalMatch:
                      IntervalMatch (SalesDate, Customer)
                      Load [Star Date], [End Date], Customer
                      Resident Discount;

                      IntervalMatch:
                      Load
                      Customer & '|' & [Star Date] & '|' & [End Date] as CustomerIntervalID,
                      Customer & '|' & [SalesDate] as CustomerDateID
                      Resident tmpIntervalMatch;

                      Drop Table tmpIntervalMatch;
                      Drop Field Customer From Sales;

                • Re: Match two table
                  hariprasad avula

                  Hi,

                  Henric is right. Use interval Match.