Wildmatch using field as value list

[bfournet]

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;

[Vegar]

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]

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

[bfournet]

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 

[Vegar]

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;