Linking fields using wildcards

[prat1507]

Hi Team

I have two tables with columns Col1 and Col2 respectively. The columns contain value as follows:

Col1:

*apple*

*cat*

Col2

apple1

apple2

apple3

I want to link the two tables on the fields above, is it possible? Please play around in the load script in order to achieve this. Any help would be appreciated.

Regards
Pratyush

[tresesco]

Have you tried MapSubString()?

[vitaliichupryna · Accepted solution]

Hi Pratyush!

I think you should make mapping with wildcard this solution was developed by Rob Wunderlich , you can find it here: 

http://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

You just need put you data in the wildmap table

LOAD * INLINE [
Key, Label
*apple*, 1
*cat*, 2
*dog*, 3
]

And then add Key to both tables as in example below

Table1:
LOAD *, $(vMapExpr(Col1)) as Key
;
LOAD *  INLINE [
Col1, Category
*apple*, fruits
*cat*, animals
]
;

Table2:
LOAD *, $(vMapExpr(Col2)) as Key
;
LOAD *  INLINE [
Customer, Col2
A, apple1
B, apple2
C, apple3
A, cat1
D, dog1
]

Thanks,

Vitalii

[prat1507]

Fantastic!! That worked, thanks a lot...... You truly saved my day!

Regards
Pratyush