Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello comunity,
i have some data where some parts have to be deleted.
i tryied to search, but could not find something suitable to delet data based on ! 2 ! conditions
fe. simply not loading unwanted data
Load *
where name <> 'name2'; //would delete all data with name2
mytable:
LOAD * INLINE
[ date, name, value
2001, name1, 1
2002, name2, 20
2003, name3, 7
2004, name2, 10
];
is it possible do delete data like:
del where (name)=name2 AND (date)=2004
any suggestions ?
// the inline data is just an example
Hi
I assume that you commented out the timestamp because it does not work. It does not work because timestamp does not exist in the source data.You also need an OR, because you want to include anything that is not sensor 12, but if it is sensor 12, you want to include dates other than 02.02.2011.
If I understood you correctly, you need the where clause to read:
Where ID_sensor<>'12' OR (mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4)) <> '02.08.2011'
Regards
Jonathan
If the data you do not want is being loaded from another source simply put
Load *
Select *
from path
Where name<>2 and date<>2004;
If it's an inline couldn't you just simply remove the data you do not want from the script?
thanks for the input.
the inline was just an example.
when i try this with a resident load,
like:
resident tablename,
Where name<>name2 and date<>2004;
i get a syntax failure. havent used where before, will sort that out.
thanks
I personaly have not used resident load before, but here is the syntax I use for a sequal load:
LOAD
company & '-' & glaccount as BalancetoGLA,
accountdesc,
active,
company,
glaccount,
segvalue1,
segvalue2,
segvalue3 ;
SQL SELECT accountdesc,
active,
coacode,
company,
glaccount,
segvalue1,
segvalue2,
segvalue3
FROM epicor904.dbo.glaccount
Where glaccount<>'';
I think it should be the same just with resident wheryou woul insert the where statement after the table name and before the semicolon:
FROM Resident epicor904.dbo.glaccount Where glaccount<>'';
Your syntax looks roughly correct. Can you post the entire load statement?
-Rob
thanks for all the suggestions, i was occupied with something else for a while.
here is an example of what the load looks like
// Thermosens
sensdata:
LOAD
ID_sensor,
mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4) as Timestamp,
data as temperature
FROM
$(vQVDPath)datapool.qvd
(qvd)
Where ID_sensor<>'12' AND temperature<>'-1000' /*timestamp<>02.08.2011*/;
# when i load the new table, every data concerning sensor 12 is missing. instead of data from 02.08.2011 (or temeratures @ -1000 which is only a test), everything is left out.
so, the "and" seems not to work.
-alex
Hi
I assume that you commented out the timestamp because it does not work. It does not work because timestamp does not exist in the source data.You also need an OR, because you want to include anything that is not sensor 12, but if it is sensor 12, you want to include dates other than 02.02.2011.
If I understood you correctly, you need the where clause to read:
Where ID_sensor<>'12' OR (mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4)) <> '02.08.2011'
Regards
Jonathan
I would code it like this:
Where NOT (ID_sensor='12' AND temperature='-1000')
I suppose this would work as well:
Where (ID_sensor<>'12' AND temperature<>'-1000')
The key in either case is the parens.
-Rob
You could also optimise this a bit like this:
sensdata:
LOAD
ID_sensor,
Date(Date#(time, 'YYYYMMDD'), 'DD.MM.YYYY') AS Timestamp,
data as temperature
FROM $(vQVDPath)datapool.qvd (qvd)
Where ID_sensor<>'12' OR Date(Date#(time, 'YYYYMMDD'), 'DD.MM.YYYY') <> '02.08.2011';
Hope that helps
Jonathan
@ Jonathan, thanks, this would be nice, if it changes 'YYYYMMDDHHMM' to 'DD.MM.YYYY' and 'HH:MM'.
i will try that, when the deleting part works.
this works fine
Where ID_sensor<>'12' OR (mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4)) <> '02.08.2011'
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.
the only solution i found, was:
1. Load QVD with "Where 11" 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 ?
//EDIT
IT IS possible
LOAD *
RESIDENT temperatures
WHERE( ID_sensor<>'12' OR timestamp<> '02.08.2011')
AND ( ID_sensor<>'11' 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