Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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,','))) ;
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
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
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;