topic Is it possible to wildmatch data from two tables? in QlikView

Is it possible to wildmatch data from two tables?

Sat, 15 Mar 2014 19:54:23 GMT

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

CustId	Name
12aazz	Jason
34bbxx	Alice
56ccyy	James
78ddww	Alice

Address

Id	Address
aazz	xxxxxxxxxxxxxxxx
34bb	yyyyyyyyyyyyyyyy
8ddw	zzzzzzzzzzzzzzzz

Please help me.

Thank you.

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

Gysbert_Wassenaar — Sat, 15 Mar 2014 19:57:28 GMT

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>'

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

nagaiank — Sat, 15 Mar 2014 20:39:47 GMT

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.