How to remove rows after load (part 3)

[#1]

Hi

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,

     DA

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

[MK_QSL]

Dexclude:

Load *, numero as tempnumero Inline

[

  numero

  7

  4

];

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;

[#3]

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

[MK_QSL · Accepted solution]

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

Inline table is used for only example purpose.

[crusader_]

Hi,

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

Dexclude:

Load *, numero as _numero Inline

[numero

  7

  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

[#6]

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

[veidlburkhard]

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

This will give you the desired result.

Regards

Burkhard

[#8]

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

[veidlburkhard]

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

    4

    7

];

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