Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

Wildmatch using field as value list

Hello !

I'm trying to load data using wildmatch function in a where clause.

Usually, this is not a problem if the matching value list is hard coded inside the script or stored in a variable.

 

However, what i need is to read the rule from a field of my table (RULE).

But it doesn't work and don't understand why ... Smiley Frustrated

 

Do you have an idea ? Did I missed something ?

 

You can test by pasting the following script. Thank you very much !

 

 

SET vMatchRule = 'B*','C*';

DATA_TMP:
LOAD * Inline [
    VALUE, RULE
    AIRPLANE, "'B*','C*'"
    BOAT, "'B*','C*'"
    CAR, "'B*','C*'"
    TRAIN, "'B*','C*'"
];


NoConcatenate
DATA:
Load
*
Resident DATA_TMP
//Where WildMatch(VALUE, RULE); // Doesn't work, why ?
Where WildMatch(VALUE, $(vMatchRule)); // Works !


DROP Table DATA_TMP;

Labels (2)
4 Replies
Partner
Partner

Re: Wildmatch using field as value list

Don't ask why, but the following tweak seems to work. 

DATA_TMP:
LOAD * Inline [
    VALUE, RULE
    AIRPLANE, "'B*','C*'"
    BOAT, "'B*','C*'"
    CAR, "'B*','C*'"
    TRAIN, "'B*','C*'"
]
 WHERE 
WildMatch(VALUE,evaluate(SubField(RULE,','))) ;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Partner
Partner

Re: Wildmatch using field as value list

Okay, this is odd ...

 

I thought that the "ValueList()" function could have helped me, but it only works in the graphical part.

To be honest i never encountered the "Evaluate" function before, your learned me something.

 

But now it works as i expected.

 

Thank you very much Vegar Smiley Very Happy

 

Partner
Partner

Re: Wildmatch using field as value list

Hello,

I tried to reproduce the same scenario but with an other dataset (see attached QVD file). It contains the nine following records :

Capture.PNG

The goal is to only keep rows , where the strings contained in field "SECURITY_KEY" doesn't start by 'B' or 'C'.

Consequently, it should only load and keep three records, those containing the words : ABRICOT, ABEILLE, ASSIETTE.

I tried to load it using the following script :

DATA:
LOAD Distinct
USER,
SECURITY_KEY,
ListeValeursExclues
FROM [lib://Bureau/DATA.qvd]
(qvd)
Where Not WildMatch(SECURITY_KEY, Evaluate(SubField(ListeValeursExclues,',')));

 

But it still doesn't work, all of the records are loaded.

 

Any idea ? This thing is really weird Smiley Mad

 

Partner
Partner

Re: Wildmatch using field as value list

The subfield()  splits your listevaleursexclude into two rows, and at leas one of them will always return zero. Therefore, will all values be included in your expressioin. You could do an workaround like my script below.

 

TMP:
LOAD 
     SECURITY_KEY
FROM
	DATA.qvd (qvd)
Where  
	WildMatch(SECURITY_KEY, Evaluate(SubField(ListeValeursExclues,','))) ;
;

LOAD 
USER, SECURITY_KEY, ListeValeursExclues FROM DATA.qvd (qvd) WHERE NOT EXISTS(SECURITY_KEY) ; DROP TABLE TMP;

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes