Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Is it possible to wildmatch data from two tables?

Hi,

I'm new to Qlikview so I would like to consult some questions and would appreciate if u guys can help.

Is it possible to use WildMatch() function for both Customer and Address table given the following tables?

I would like to get the customer address from Address table if CustId has match with Id.

Expected result will be getting address for 12aazz,34bbxx and 56ccyy.

Like WildMatch(CustId,Id) works or is there any other way to get the same result?

Customer

CustIdName
12aazzJason
34bbxxAlice
56ccyyJames
78ddwwAlice

Address

IdAddress
aazzxxxxxxxxxxxxxxxx
34bbyyyyyyyyyyyyyyyy
8ddwzzzzzzzzzzzzzzzz

Please help me.

Thank you.

2 Replies

Re: Is it possible to wildmatch data from two tables?

Have a look at the mapsubstring function:

The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

mapsubstring('mapname', expr)

This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'


talk is cheap, supply exceeds demand
nagaiank
Valued Contributor III

Re: Is it possible to wildmatch data from two tables?

For your sample data, the following script gives the result. (This may not be a desired solution when the number of rows in each table is large.)

Temp:

LOAD * Inline [
CustId,Name
12aazz,Jason
34bbxx,Alice
56ccyy,James
78ddww,Alice
]
;
Outer Join (Temp)
LOAD * Inline [
Id,Address
aazz,xxxxxxxxxxxxxxxx
34bb,yyyyyyyyyyyyyyyy
8ddw,zzzzzzzzzzzzzzzz
]
;

Result:
NoConcatenate
LOAD CustId, Name, Id, Address Resident Temp
Where Index(CustId, Id) > 0;

DROP Table Temp;

The qvw file is  attached.

Community Browser