topic Re:Delete data after reload in QlikView

Delete data after reload

Fri, 01 Oct 2010 20:57:37 GMT

Hello:

The question is:

After reload data to my qlickview, with one SQL SELECT, is possible delete rows of this data, with some command? for example

After SQL SELECT

DATE	Nunber	Cod	AMOUNT
11/10/2010	10	cod1	10
19/10/2010	20	cod1	115
12/11/2010	22	cod1	200
23/12/2010	30	cod3	25
15/09/2010	40	cod1	40
28/10/2010	255	cod1	900
01/11/2010	30	cod3	25
14/12/2010	15	cod1	100

The data with the same number drop

the result

DATE	Nunber	Cod	AMOUNT
11/10/2010	10	cod1	10
19/10/2010	20	cod1	115
12/11/2010	22	cod1	200
15/09/2010	40	cod1	40
28/10/2010	255	cod1	900
14/12/2010	15	cod1	100

Any idea?

Thanks!!, regards

Cèsar Estrada

Delete data after reload

Fri, 01 Oct 2010 21:02:59 GMT

Hi there, I dont quiet undertand the pattern you want to remove, do you want to remove rows with Amount equal to 25?? or rows with Cod equal to 30cod3??

For either case try something similar to the following:

Table:

Load *

where Cod <> '30cod3';

sql select *

from database;

Regards

Re:Delete data after reload

Fri, 01 Oct 2010 21:07:49 GMT

Hi Ivan

I need delete row with the combination with the same number, code and amount.

Thanks!

Re:Delete data after reload

Fri, 01 Oct 2010 21:26:58 GMT

Hi there, one thing i forgot is that is not recommended to do any filtering while you are load from the database, because it affects performance, for instance you can save the table in a qvd.

Try something similar to the following:

Test:

//replace with your sql load
LOAD * INLINE [
Prod, Date, Amount
A, 10/02/2000, 25
A, 11/02/2000, 25
B, 10/02/2000, 30
B, 20/02/2000, 30
B, 23/02/2000, 20
C, 1/02/2000, 45
D, 21/02/2000, 50
D, 22/02/2000, 50
];

store Test into Test.qvd;
drop table Test;

Test:
Load Prod, date(max(date#(Date,'DD/MM/YYYY'))) as Date, only(Amount) as Amount
from Test.qvd (qvd)
group by Prod, Amount;

Regards.