Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Join statement for non-matching values

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 groupDiscount %
Group.1020
Group.10030
Group.10230
Group.10630

-gives-

  

Result
Product group

Discount %

Group.10030
Group.10120
Group.10230
Group.10320
Group.10420
Group.10520
Group.10630
4 Replies

Re: Join statement for non-matching values

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

Not applicable

Re: Join statement for non-matching values

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!

Re: Join statement for non-matching values

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

Not applicable

Re: Join statement for non-matching values

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:

CustomerProduct groupDiscount
1110
11020
110130
110230
110330
2117
210150
210250
210350
3115
31025
310130
310230
310330

Result after assigning customer discounts to product groups in our item database.

CustomerProduct groupApplied discount
110020
110130
110230
110330
110420
110520
210017
210150
210250
210350
210417
210517
3115
310025
310130
310230
310330
310425
310525

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!

Community Browser