topic Joining Tables Using Wildcards in QlikView

Joining Tables Using Wildcards

Thu, 20 Jul 2017 18:46:31 GMT

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

Re: Joining Tables Using Wildcards

Anonymous — Thu, 28 Sep 2017 09:36:32 GMT

Have you got solution

Re: Joining Tables Using Wildcards

felipedl — Thu, 28 Sep 2017 18:29:07 GMT

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;

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.