topic Re: Wildmatch using field as value list in App Development

Wildmatch using field as value list

bfournet — Sat, 16 Nov 2024 06:39:55 GMT

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

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;

Re: Wildmatch using field as value list

Vegar — Tue, 05 Feb 2019 21:32:01 GMT

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,',')))
;

Re: Wildmatch using field as value list

bfournet — Tue, 05 Feb 2019 21:52:17 GMT

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

Re: Wildmatch using field as value list

bfournet — Tue, 12 Feb 2019 15:06:09 GMT

Hello,

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

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

Re: Wildmatch using field as value list

Vegar — Tue, 12 Feb 2019 21:16:15 GMT

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;