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

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

andy — Tue, 10 Nov 2009 18:02:39 GMT

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

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

Tue, 10 Nov 2009 18:04:55 GMT

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

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

Miguel_Angel_Baeyens — Tue, 10 Nov 2009 18:25:57 GMT

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.

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

andy — Tue, 10 Nov 2009 18:32:25 GMT

Thanks guys!

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

The given solution works after I replace " with ' .

Brgds

Andy

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

Tue, 10 Nov 2009 18:34:20 GMT

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

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

burgersurfer — Tue, 26 Jan 2010 23:47:40 GMT

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.

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

johnw — Wed, 27 Jan 2010 00:00:50 GMT

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

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

burgersurfer — Wed, 27 Jan 2010 00:14:09 GMT

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

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

Anonymous — Sat, 15 Jan 2011 00:12:10 GMT

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

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

johnw — Sat, 15 Jan 2011 00:44:22 GMT

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