Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I've started this discussion in hope you guys can help me with the following issue. I have a customer database wherein customer discounts are assigned to product groups. I want to match these customer discounts with the product groups in our item database. Therefore I am looking for a join command.
As you can see in the example hereunder I have three digit product groups in the item database. My customer database does not have a discount for every three digit product group. So a left join or a inner join would return only three rows with a discount. However, I do want to have a discount for every product group. Therefore I want to assign the discount in the two digit product group for each three digit product group that I can't match.
Examples and the desired result are stated below. I would really appreciate your help.
Kind regards,
Robin
Item database |
---|
Product group |
Group.100 |
Group.101 |
Group.102 |
Group.103 |
Group.104 |
Group.105 |
Group.106 |
-join statement-
Customer database | |
---|---|
Product group | Discount % |
Group.10 | 20 |
Group.100 | 30 |
Group.102 | 30 |
Group.106 | 30 |
-gives-
Result | |
---|---|
Product group | Discount % |
Group.100 | 30 |
Group.101 | 20 |
Group.102 | 30 |
Group.103 | 20 |
Group.104 | 20 |
Group.105 | 20 |
Group.106 | 30 |
Try this..
CustomerDataBase:
Mapping
LOAD * INLINE [
Product group, Discount %
10, 20
100, 30
102, 30
106, 30
];
ItemDataBase:
LOAD [Product group],ApplyMap('CustomerDataBase',[Product group],
ApplyMap('CustomerDataBase',Left([Product group],2),'Unknown%')) as Discount%;
LOAD * INLINE [
Product group
100
101
102
103
104
105
106
];
Hello Settu,
Thank you for your reply. Your code works for the given example, only in practice we have over 300 different product groups with discounts assigned for over 1000 customer records. So the real database is a little more comprehensive then I illustrated. Discounts are assigned to the product groups in hierarchical order:
Three digit product group discount, if no discount assigned, then
-> Two digit product group discount, if no discount assigned, then
-> One digit product group discount, if no discount assigned, then
-> Null
Is there a way to use the mapping statement (or any other statement) without defining every single value?
Thanks!
Still you can use the multiple Apply map based on your hirarchy count.. can you post the sample which is illustrate your original issue?
Hello Settu,
Couldn't get to you sooner because of my vacation. I will make an effort to illustrate my problem better.
Customer table with discounts per product group:
Customer | Product group | Discount |
---|---|---|
1 | 1 | 10 |
1 | 10 | 20 |
1 | 101 | 30 |
1 | 102 | 30 |
1 | 103 | 30 |
2 | 1 | 17 |
2 | 101 | 50 |
2 | 102 | 50 |
2 | 103 | 50 |
3 | 1 | 15 |
3 | 10 | 25 |
3 | 101 | 30 |
3 | 102 | 30 |
3 | 103 | 30 |
Result after assigning customer discounts to product groups in our item database.
Customer | Product group | Applied discount |
---|---|---|
1 | 100 | 20 |
1 | 101 | 30 |
1 | 102 | 30 |
1 | 103 | 30 |
1 | 104 | 20 |
1 | 105 | 20 |
2 | 100 | 17 |
2 | 101 | 50 |
2 | 102 | 50 |
2 | 103 | 50 |
2 | 104 | 17 |
2 | 105 | 17 |
3 | 1 | 15 |
3 | 100 | 25 |
3 | 101 | 30 |
3 | 102 | 30 |
3 | 103 | 30 |
3 | 104 | 25 |
3 | 105 | 25 |
Customer 1 as example. Where there is no three-digit product group discount known, the two-digit product group discount is applied. Therefore the assigned discount for product groups 100, 104 and 105 values 20.
Customer 2 as example. Where there is no three-digit-discount known, the two-digit-discount is applied. However, in case of customer 2 there is no two-digit-discount known. Therefore discount of the one-digit product group discount is applied for product groups 100, 104 and 105, which values 17.
This is just an example. Our database is much more comprehensive so I am looking for a geneal logical (join?) statement to get the discount appropriately assigned, rather than a mapping statement.
Thanks in advance!