Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michielvandegoo
Specialist
Specialist

Match 3 different values and wildmatch and if then

I'm trying to get this to work:

I need to find the matches of course.

Table 1 is the defintion

ContractOrDeSite
A049051XYZ
A 051ABC

 

Table 2 is the source data

ContractOrDe
A049051
ADHDFD051
ACADVGDKSDJ

 

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. 

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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
	]
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
martinpohl
Partner
Partner

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

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
	]
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

michielvandegoo
Specialist
Specialist
Author

Of course!!!

Didn't think about this one.

Works 100%

 

Thanks!