topic How to remove rows after load (part 3) in QlikView

How to remove rows after load (part 3)

Tue, 07 Jul 2015 08:37:03 GMT

I am still messing up with removing rows after load:

Below is my initial table Dan:

numero	DA
1	Goldorak
2	Candy
3	DBZ
4	DBGT
4	Booba
5	Nicky Larsson
6	Bugs Bunny
7	Captain Flam
8	Albator
9	Avatar ANG
10	Avatar KORA

I'd like to exclude several lines using a list in a separate table. Let's say I'd like to remove lines with numero values 4 and 7.

My code is

>>>

dan:

LOAD numero,

FROM

(ooxml, embedded labels, table is Sheet1);

dan2:

NoConcatenate

load *

resident dan

where not exists(Dexclude,numero);

DROP Table dan;

DROP Table Dexclude;

>>>

I previously load table Dexclude as

numero
4
7

I am ending with an empty dan2 table without any processing error. it seems possible to exclude using

>>>

where numero<>4 and numero<>7

>>>

but I'd like to be more efficient in my code

I was expecting

numero	DA
1	Goldorak
2	Candy
3	DBZ
5	Nicky Larsson
6	Bugs Bunny
8	Albator
9	Avatar ANG
10	Avatar KORA

Your help will be highly appreciated.

Pierre

Re: How to remove rows after load (part 3)

MK_QSL — Tue, 07 Jul 2015 08:44:17 GMT

Dexclude:

Load *, numero as tempnumero Inline

[

numero

];

dan:

Load * Inline

[

numero, DA

1, Goldorak

2, Candy

3, DBZ

4, DBGT

4, Booba

5, Nicky Larsson

6, Bugs Bunny

7, Captain Flam

8, Albator

9, Avatar ANG

10, Avatar KORA

];

dan2:

NoConcatenate

load *

resident dan

where not exists(tempnumero,numero);

DROP Table dan;

DROP Table Dexclude;

Re: How to remove rows after load (part 3)

Tue, 07 Jul 2015 08:54:01 GMT

Thanks Manish,

it seems to work. But how do you make it work when the exclusions are in an external file as in my case ?

thanks again,

Pierre

Re: How to remove rows after load (part 3)

MK_QSL — Tue, 07 Jul 2015 08:56:21 GMT

Instead of Inline Load you can use Load * from ExternalFile...

Inline table is used for only example purpose.

Re: How to remove rows after load (part 3)

crusader_ — Tue, 07 Jul 2015 08:57:44 GMT

Hi,

Use Preceding Load instead of resident load and script will be like below:

Dexclude:

Load *, numero as _numero Inline

[numero

4];

dan:

LOAD *

where not exists(_numero,numero);

Load * Inline

[numero, DA

1, Goldorak

2, Candy

3, DBZ

4, DBGT

4, Booba

5, Nicky Larsson

6, Bugs Bunny

7, Captain Flam

8, Albator

9, Avatar ANG

10, Avatar KORA];

DROP Table Dexclude;

Hope this helps.

Andrei Kaliahin

Re: How to remove rows after load (part 3)

Tue, 07 Jul 2015 09:03:58 GMT

Thank you all,

I now know why it did not work: I used twice the same name! Thank you for helping me being smarter!

Pierre

Re: How to remove rows after load (part 3)

veidlburkhard — Tue, 07 Jul 2015 09:05:36 GMT

Hi Pierre, why not see it positive and do it like this:

This will give you the desired result.

Regards

Burkhard

Re: How to remove rows after load (part 3)

Tue, 07 Jul 2015 09:15:24 GMT

Hi Burkhard,

thanks for your input, I did not think about the left join. Key is that lines to exclude are known after initial. And using an exclusion list lets you deal with a smaller table. I might have a situation where your option will be the one to apply so thanks again and regards,

Pierre

Re: How to remove rows after load (part 3)

veidlburkhard — Tue, 07 Jul 2015 18:43:24 GMT

Hi Pierre,

I agree a smaller table with the excluded values only makes sense.

Why not use a variable like in the below solution:

Dexclude:

LOAD * INLINE [

numero1

];

Dexclude1:

LOAD

Concat(numero1, ',') as Exclude //build a comma separated concatenation of your excluding table.

Resident Dexclude;

DROP Table Dexclude;

LET vExclude = Peek('Exclude', 0, 'Dexclude1');

DROP Table Dexclude1;

Dan:

LOAD * INLINE [

numero, DA

1, Goldorak

2, Candy

3, DBZ

4, DBGT

4, Booba

5, Nicky Larsson

6, Bugs Bunny

7, Captain Flam

8, Albator

9, Avatar ANG

10, Avatar KORA

]Where not Match(numero, $(vExclude));

This will give you the result you are looking for.

Happy qliking

Burkhard