Match 3 different values and wildmatch and if then

[Michiel_QV_Fan]

I'm trying to get this to work:

I need to find the matches of course.

Table 1 is the defintion

Contract	Or	De	Site
A	049	051	XYZ
A		051	ABC

Table 2 is the source data

Contract	Or	De
A	049	051
A	DHDFD	051
A	CADVG	DKSDJ

My challenge is that in Table 2 the result for A-049-051 must be XYZ

and that in Table 2 the result for A-DHDFD-051 must be ABC but the column Or in Table 1 must be treated as a wildcard. All values from De = 051 without a specific value in Or result in Site ABC.

I tried loops and if statements but cannot get it to work with the sort off open interval from Table 1. 

[martinpohl]

Hi,

load your table1 and table2.

then left join (Table2) load

Contract,

Or,

De,

Site as Site1

resident Table1;

left join (Table2) load

Contract,

De

Site as Site2

resident Table1;

Table3:

load

Contract,

Or,

De,

if(len(Stite1)>0,Site1,Site2) as Site

resident Table2;

drop tables Table1, Table2;

Regards

[Vegar · Accepted solution]

You could make use of @rwunderlich  QlikView Components project. It has a functionality called qvc.CreateWildMapExpression that could help you solve this issue. 

Please examine my sample code below.

set NullInterpret='';  //Empty values in inline will be intepreted ass null
NULLASVALUE  Or, De;  //set null as value for listed fields
set NullValue='*';    // Let the nullvalue be *. 

TMP:
LOAD * Inline [
	Contract,Or, De, Site
	A, 		 049,051,XYZ
	A,		    ,051,ABC
]; 

[WildmatchTable]:
LOAD
	Contract &'|'& Or &'|'& De as  WildmatchKey,
	Site  as WildmatchValue
RESIDENT TMP;
DROP TABLE TMP;

/*Include the QVC. It is also downloadable for local use*/
$(Must_Include=http://raw.githubusercontent.com/RobWunderlich/Qlikview-Components/master/Qvc_Runtime/Qvc.qvs);
CALL Qvc.CreateWildMapExpression (vMapExpr, 'WildmatchTable');
DROP TABLE WildmatchTable;


[Table 2]:
LOAD $(vMapExpr(WildmatchedKey))  as WildmatchedValue, *
;
LOAD
Contract &'|'& Or &'|'& De as WildmatchedKey, 
*
Inline [
	Contract, Or, De
	A, 049, 051
	A, DHDFD, 051
	A, CADVG, DKSDJ
	]
;

[Michiel_QV_Fan]

Of course!!!

Didn't think about this one.

Works 100%

Thanks!