Joining Tables Using Wildcards

[#1]

Hey Community,

I'm trying to map a table of clients and codes corresponding to product elections with a table of products and the code that implies that the product in question has been purchased. Hopefully the tables below will clear up what I mean:

Clients:                                                                                 

Client	Mapping Code
A	1200
B	20140
C	01100
D	32140

Products:

Mapping Code	Product ID	Product Name
1*	0	a
2*	0	a
1*	1	c
2*	2	d
?11*	3	e
?2*	4	f
3?14*	5	g

where the Product mapping code follows the standard convention of '?' representing any single character and '*' any single or combination of characters.

I was able to employ the "Mapping With WIldcards" recipe from Rob Wunderlich's site (Qlikview Cookbook | Recipes for Qlikview Success) within a while loop (see attached QVW), followed by a Left Join to achieve what I guess is effectively a "Wild Left Join":

"Joining" the above tables in this manner yielded precisely what I sought, when the appropriate fields were places in a table box:

Client	Product ID	Product Name
A	0	a
A	1	c
A	4	f
B	0	a
B	2	d
C	3	e
D	4	f
D	5	g

However, when I try to use the same method on non-dummy data (say, a  545-row list of products and a 25000-row list of clients), I don't get an error during the reload, but when I create the table box, I see that many clients are not properly mapped to their respective products. For example, client 'A' in the above tables might only be mapped to products 1 and 4, or even just product 1.

Would anyone be able to offer any thoughts as to why this might be happening?

Thank you in advance for your help.

Thanks,

Elliot

[Anonymous]

Have you got solution

[felipedl]

Hi Elliot,

I've done a simple solution for it, but the problem is it joins all the products into the client list, creating a whole lot of extra rows that wouldn't need to be created but at the end, only the needed rows are left in the final table.

Since you have few lines of products and clients, i believe it will perform ok, but as it escalates it will be a nightmare.

Bellow the code:

Products:

Load * Inline

[

Client,Mapping Code

A, 1200

B, 20140

C, 01100

D, 32140

];

MappingCode:

Load * Inline

[

Mapping, Product ID, Product Name

1*, 0, a

2*, 0, a

1*, 1, c

2*, 2, d

?11*, 3, e

?2*, 4, f

3?14*, 5, g

];

join(Products)

Load

Mapping

Resident MappingCode;

x:

Load

*,

WildMatch([Mapping Code],Mapping) as [Flag Mapped]

Resident Products;

left join(x)

Load

Mapping,

[Product ID],

[Product Name]

Resident MappingCode;

NoConcatenate

finalTable:

Load

*

Resident x

where [Flag Mapped] = 1;

drop tables Products,MappingCode,x;

drop fields [Mapping Code],Mapping,[Flag Mapped] from finalTable;

Felipe.