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

deleting range of data from loaded tables

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

9 Replies
Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Your syntax looks roughly correct. Can you post the entire load statement?

-Rob

Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

@ 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