Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to remove rows after load (part 3)

Hi

I am still messing up with removing rows after load:

Below is my initial table Dan:

numeroDA

1

Goldorak
2Candy
3DBZ
4DBGT
4Booba
5Nicky Larsson
6Bugs Bunny
7Captain Flam
8Albator
9Avatar ANG
10Avatar 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

numeroDA

1

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

Your help will be highly appreciated.

Pierre

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

Inline table is used for only example purpose.

View solution in original post

8 Replies
MK_QSL
MVP
MVP

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;

Not applicable
Author

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

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

Inline table is used for only example purpose.

crusader_
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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
Creator III
Creator III

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

numero.jpg

This will give you the desired result.

Regards

Burkhard

Not applicable
Author

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
Creator III
Creator III

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