Join statement for non-matching values

[#1]

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

[settu_periasamy]

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
];

[#3]

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!

[settu_periasamy]

Still you can use the multiple Apply map based on your hirarchy count.. can you post the sample which is illustrate your original issue?

[#5]

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!