Skip to main content

App Development

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
bfournet
Partner - Contributor II
Partner - Contributor II

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

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,','))) ;
bfournet
Partner - Contributor II
Partner - Contributor II
Author

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

 

bfournet
Partner - Contributor II
Partner - Contributor II
Author

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

 

Vegar
MVP
MVP

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;