topic Re: using WHERE in LOAD more than once in QlikView

using WHERE in LOAD more than once

Tue, 27 Sep 2011 11:09:33 GMT

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

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

using WHERE in LOAD more than once

SunilChauhan — Tue, 27 Sep 2011 11:25:06 GMT

no its not

we can have one where statement per table.

using WHERE in LOAD more than once

Tue, 27 Sep 2011 12:02:18 GMT

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

Re: using WHERE in LOAD more than once

john_duffy — Tue, 27 Sep 2011 12:20:05 GMT

Hello.

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

using WHERE in LOAD more than once

Tue, 27 Sep 2011 13:34:17 GMT

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

Re: using WHERE in LOAD more than once

gussfish — Tue, 27 Sep 2011 22:43:37 GMT

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.