Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Joining Tables Using Wildcards

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:                                                                                 

ClientMapping Code
A1200
B20140
C01100
D32140

Products:

Mapping CodeProduct IDProduct Name
1*0a
2*0a
1*1c
2*2d
?11*3e
?2*4f
3?14*5g

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:

ClientProduct IDProduct Name
A0a
A1c
A4f
B0a
B2d
C3e
D4f
D5g

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

2 Replies
sbabubo9
New Contributor

Re: Joining Tables Using Wildcards

Have you got solution

felipedl
Valued Contributor III

Re: Joining Tables Using Wildcards

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.

Community Browser