Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

using WHERE in LOAD more than once

Hello community,

im trying to delete some data from a table.

the example its about temperature and sensors.

if the data of sensors 11 and 12 has to be deleted on a certain date(s), WHILE is used to delete them on LOAD.

but i didnt find anything in the reference or in the community about using 2 or more WHERE cases while loading data.

so, if i want do delete ID_sensor 12 and 11, this is not possible at the moment.

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE ID_sensor<>'12' OR timestamp<> '02.08.2011',

WHERE ID_sensor<>'11' OR timestamp<> '02.08.2011';

OR

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE ID_sensor<>'12' OR timestamp<> '02.08.2011' AND

/*WHERE*/ ID_sensor<>'11' OR timestamp<> '02.08.2011';

the only solution i found, was:

1. Load QVD with "WHERE11" case (Table 1, new name)

2. ResidentLoad with "WHERE 12" case (Table 2, former name of table 1)

3. droptable (Table 1)

is it possible, to use WHERE more than once per table ?

thanks, alex

1 Solution

Accepted Solutions
Not applicable
Author

Does this work? Using parenthesis should tell it how to group.

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE( ID_sensor<>'12' and timestamp<> '02.08.2011') AND

( ID_sensor<>'11' and timestamp<> '02.08.2011');

View solution in original post

5 Replies
SunilChauhan
Champion
Champion

no its not

we can have one where statement per table.

Sunil Chauhan
Not applicable
Author

Does this work? Using parenthesis should tell it how to group.

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE( ID_sensor<>'12' and timestamp<> '02.08.2011') AND

( ID_sensor<>'11' and timestamp<> '02.08.2011');

john_duffy
Partner - Creator III
Partner - Creator III

Hello.

How about Where Timestamp <> '02.08.2011' and not match (ID_Sensor,11,12).

Not applicable
Author

it does work !

i thought, its not possible to use more expressions in one load.

LOAD *

RESIDENT temperatures

WHERE( ID_sensor<>'12' OR timestamp<> '02.08.2011') AND

( ID_sensor<>'11' OR timestamp<> '02.08.2011');

It deletes all data from sensors 11 and 12 on date 02.08.2011

thanks,

this saved a lot of code

the resident load stunt used 70+ lines of code

gussfish
Creator II
Creator II

Hi Alexander,

the trick here is to invert your conditions, just so:

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE not

((ID_sensor='12' AND timestamp= '02.08.2011')

OR (ID_sensor='11' AND timestamp= '02.08.2011'));

This can, of course, then be refactored into

Temperatures2:

LOAD *

RESIDENT temperatures

WHERE not ( (ID_sensor='11' or ID_sensor='12') AND timestamp=

'02.08.2011' );

If you're considering listing lots of sensors to be excluded, consider

replacing " ID_sensor='11' or ID_sensor='12' " with "

match(ID_sensor,'11','12')<>0 "

And finally, if you're going to want to change the list of sensors on

any regular basis, then avoid changing your code each time by listing

them in a CSV or XLS file, loading them in, and then using a JOIN or

KEEP to filter. But I imagine you'll need further assistance from this

forum if you decide to go down that path.

Angus.