Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
MVP
MVP

deleting range of data from loaded tables

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
9 Replies
Not applicable

deleting range of data from loaded tables

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

deleting range of data from loaded tables

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

Re: deleting range of data from loaded tables

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

deleting range of data from loaded tables

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

-Rob

Not applicable

deleting range of data from loaded tables

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

MVP
MVP

deleting range of data from loaded tables

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

deleting range of data from loaded tables

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

MVP
MVP

deleting range of data from loaded tables

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

Re: deleting range of data from loaded tables

@ 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

Community Browser