Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have got two tables
Transactions table:
Date | Client code | Product ID | Quantity | Price | Amount |
---|---|---|---|---|---|
2014-01-05 | UAB253 | 0101001 | 10 | 5.4 | 54 |
2014-02-10 | UAB253 | 0101002 | 15 | 10 | 150 |
2014-03-20 | UAB260 | 0101002 | 4 | 4 | 16 |
Discounts table:
Client code | DateFrom | DateTo | Discount type | Discount amount |
---|---|---|---|---|
UAB253 | 2014-01-01 | 2014-01-31 | 1 | 5% |
UAB253 | 2014-01-01 | 2014-04-30 | 2 | 10% |
UAB260 | 2014-01-01 | open | 3 | 0.5 $/quantity |
I want to obtain a real price for each transaction. Discounts are only client specific.
How to I go about creating a correct data model?
Hi,
What happened when you have 2 discounts in the same period of time for the same customer?
Line 1 in the Transactions table and line 1 & 2 in the Discounts table.
Ariel
If two or more discounts apply for the same date, then they must be aggregated. In this case it would be 5%+10% = 15%. However, type 3 discount is absolute number and it must be applied after percentile discounts were applied.
Hi
This is a case for the slowly changing dimension (SCD) version of interval match:
Transactions: // load fields as per your post
LOAD .....
Discounts: // load fields as per your post, but...
LOAD .....
If (DateTo = 'open', null(), DateTo), // 'open' will be achieved with a null date to
....
Left Join (Transactions)
IntervalMatch(Date, ClientCode)
LOAD DateFrom,
DateTo,
ClientCode
Resident Discounts;
// --- Optional code to bring the discount amounts into the transaction table:
Left Join (Transactions)
LOAD * Discounts;
Drop Table Discounts; // table not required any more
Drop Fields DateFrom, DateTo // optional field cleanup
// --- End optional code
HTH
Jonathan
Hi,
Please See the Attachmant.
Hope this will help you.
BR
Ariel
Thanks, Jonathan,
however, your script returns me transactions table with increased amount of lines. It duplicates or triplicates number of transaction lines depending on how many discounts apply for the same date.
While Ariel's script was more helpful, it returns sumed values for discounts. I would like to return a transactions table in a form like this:
Date | Client code | Product ID | Quantity | Price | AmountD | Discount1 | Discount2 | Discount3 | Discount4 | Discount5 | DiscountX |
---|---|---|---|---|---|---|---|---|---|---|---|
Can You help me on this?
Actually I used Ariel's script to solve my problem. Thanks Ariel.