5 Replies Latest reply: May 25, 2011 2:36 AM by Janus Stensig Skov RSS

    Add field to transaction table (match on multiple fields)

    Janus Stensig Skov

      Hi,

      I'm trying to find a way to add Supplier bonus to my Sales-transaction table.

       

      "Supplier bonus"-table:

      ProductID

      Chain (Supplier-bonus is releated to specific customer-groups = Chains)

      DateStart (Startdate - typically first day in a quater, but sometimes first day in a year or month)

      DateEnd (Enddate - typically last day in a quater, but sometimes last day in a year or month)

      Bonus (Bonus for each ProductID, in the given Chain, between DateStart and DateStart)

       

      "Sales-transaction" (relevant fields)

      ProductID

      Chain (to which Chain is the transaction-line related)

      AccountingDate

       

       

      I think that ApplyMap is the way to go and it´s pretty simple if I only had to map on ProductID & Chain, but the trouble is the date-part (DateStart/DateEnd vs. AccountingDate).

      I can't figure out how to handle the date-part, sometimes Supplier bonus is related to quater, sometimes a year etc etc.

       

      Any ideas out there?

        • Add field to transaction table (match on multiple fields)
          Janus Stensig Skov

          I wonder if a combination of ApplyMap and IntervalMatch could do the job ... but how do I set this up?

            • Add field to transaction table (match on multiple fields)

              Add this to both tables

              [Product ID] & '|' & [Chain] as '_product_id_chain'

               

              Now comment out ProductID and Chain from the "Sales-Transaction" table

               

              Now add this:

               

              LEFT JOIN ("Sales-transaction") LOAD

                   _product_id_chain,

                   Bonus

              RESIDENT "Supplier bonus";

               

              You're still going to have synthetic keys if you do it this way but you said you wanted bonus in the Sales transaction table so there's no getting around it unless you just do the first thing I said and ignore the part with the left join.  Personally I would just comment out Product ID and Chain from the Sales Transaction Table, add the _product_id_chain field, and be done with it.  I don't really see why you need bonus in that table.

                • Add field to transaction table (match on multiple fields)
                  Janus Stensig Skov

                  Hi Trent,

                  Thank you for replying. I see your point, but still misses the date-part. Here comes an example:

                  ProductID 1

                  Supplier gives a bonus of $1 per sold good in first half of 2010, for customers belonging to Chain A

                  Supplier gives a bonus of $1.1 per sold good in second half of 2010, for customers belonging to Chain A

                  Supplier gives a bonus of $0.8 per sold good in 2010, for customers belonging to Chain B

                   

                  Summary:

                  Chain A: bonus for ProductID=1 is different between first and second half of 2010 ($1 vs. $1.1)

                  Chain B: bonus for ProductID=1 is the same during 2010 ($0.8)

                   

                  Hope that it  explains better what I'm struggling with. I look forward to hearing from you again, also if you think I can do this in another way

                    • Add field to transaction table (match on multiple fields)
                      susant Kumar swain

                      Hi ,

                       

                      If u use applymap between the two tables in the final table u are going to add the column AccountingDate

                       

                      becoz Product and Chain is present in both the tables so If u use only intervalmatch to add the AccountingDate

                       

                      it will be added for the DateStart and DateEnd which is available for a product and chain .So i think in this case

                       

                      Intervalmatch alone is enough . If i am wrong in understanding ur scenario please let me know

                        • Add field to transaction table (match on multiple fields)
                          Janus Stensig Skov

                          Hi,

                          I got it working - thanks for your replies

                           

                          Solution:

                          left join intervalmatch (AccountingDate,ProductID,Chain) load StartDate,EndDate,ProductID,Chain resident SupplierBonus;   

                          LEFT JOIN (Sales-transaction) LOAD * Resident SupplierBonus;

                           

                          2 things caused me problems, but here is the fixes:

                          1) Format of AccountingDate,StartDate and EndDate - I had to make an explicit formatting of each field (Date#)

                          2) How to get the field Bonus (from SupplierBonus) - I added the last left join

                           

                          Have a nice day