Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
no its not
we can have one where statement per table.
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');
Hello.
How about Where Timestamp <> '02.08.2011' and not match (ID_Sensor,11,12).
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
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.