Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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.

View solution in original post

crusader_
Partner
Partner

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