Which operators are allowed for the 'where' clause when doing LOAD from residents?

[andy]

Hi guys,

I want to load the ID field from a resident if another field named CODE in the example below has certain values.

It seems that the in-operator isn't allowed

(I cannot find which operators that are allowed for the LOAD statement only for the SQL SELECT statement)

Do I have to add 13 WHERE CODE='A1 AND CODE='A7' .....and so on ?

LOAD
ID
RESIDENT A where CODE in ("A1","A7","B1","B3","B7","L1","M1","N1","C1","D1","E1","F1","G1");

/Andy

[#2 · Accepted solution]

Hi,

You can do the following:

LOAD
ID
RESIDENT A where MATCH(CODE,"A1","A7","B1","B3","B7","L1","M1","N1","C1","D1","E1","F1","G1");

Rgds

[Miguel_Angel_Baeyens]

Hello Andy,

Take into account that WHERE following LOAD in QlikView script is not SQL, although some like EXISTS or LIKE work fine. So the limits are any of the functions and expressions you can use with the script. For your specific purpose, the previous example is right.

Answering your question, use any of the functions you have available for chars, strings, math and so.

Regards.

[andy]

Thanks guys!

I appreciate that little explanation that I can use any Qlikview function.

The given solution works after I replace " with ' .

Brgds

Andy

[#5]

Sorry Andy my mistakes, you're right, strings are in simple quotes (') and not " !

[burgersurfer]

Thanks for this post, it put me on the righ track

But how do I match two different fields?

I need to match a transaction type, and a category grouping from a sales table in one load. So I want to see "Sales" only as transaction type, and "Shoes" as category type in my data:

Load
TT_ID,
Category,
....
From <csv file> where
MATCH(TT_ID, 'Sales') AND MATCH(Category, 'Shoes')
;

does not work.

[johnw]

My eyesight must be failing me. Looks good to me, though when there's only one value, I just use the equals sign.

LOAD ...
FROM ...
WHERE TT_ID = 'Sales'
AND Category = 'Shoes'
;

[burgersurfer]

Mmm,

thanks John

I was using the renamed field in the second match, not the original. So using CatCode instead of the Category I renamed it to, works fine

.

[Anonymous]

What if I am trying to exclude values. When I used the qvd "NOT IN" Doesnt work.

Where "PROD_SR_SN_HIERARCHY_PRODUCT_GTS" NOT IN ('Cancelled Call','GTS Testing','ProActive_Support')

What can I used in a Where clause to exclude records.

Thank you,

Cassadi

[johnw]

WHERE NOT MATCH("PROD_SR_SN_HIERARCHY_PRODUCT_GTS",'Cancelled Call','GTS Testing','ProActive_Support')