Add field to transaction table (match on multiple fields)

[jstensig]

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?

[jstensig]

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

[#3]

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.

[jstensig]

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

[qliksus · Accepted solution]

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

[jstensig]

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