Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

andy
Contributor II

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

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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

9 Replies
Not applicable

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

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?

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
Contributor II

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

Thanks guys!

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

The given solution works after I replace " with ' .

Brgds

Andy

Not applicable

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

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

burgersurfer
New Contributor III

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

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.

MVP
MVP

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

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
New Contributor III

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

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

.

cassadi44
New Contributor II

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

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



MVP
MVP

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

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