Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Instead of Inline Load you can use Load * from ExternalFile...
Inline table is used for only example purpose.
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;
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
Instead of Inline Load you can use Load * from ExternalFile...
Inline table is used for only example purpose.
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
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
Hi Pierre, why not see it positive and do it like this:
This will give you the desired result.
Regards
Burkhard
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
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