Reverse inner keep

[Anonymous]

Greetings everyone,

I have 2 tables like those:

Name	School
Franck Damico	Berclay
Mark Brendanawicz	Harvard
Ramuh Leviathan	Centrale
Jake Peralta	NY Excelsior

School
Berclay
Harvard

First table is my Principal table, the second one is my exclusion table.

I want the data that are on my second table to be removed from the first one to have a result like this

Name	School
Ramuh Leviathan	Centrale
Jake Peralta	NY Excelsior

I was trying to use keep but i couldn't find a way to revert it to keep the data that are NOT in another table.

Thanks for the answers!

[undergrinder]

Hi Vincent,

you can't exclude the second table's values in one step.

Please look into this piece of code:

tmp:
Load
     Name,
     School
From/Resident [table];

left join(tmp)
Load
     School
     1 as delete_flg
From/Resident [exclude table];

Final:
Load
     Name,
     School
Resident tmp
Where delete_flg=1;

drop table tmp;

So first step is join the exclude table to the base table, with a flag, and in the next step you can filter out the values based on the previously created flag.

G.

[Ivan_Bozov · Accepted solution]

Try as follows:

Table_1:
LOAD
     *
INLINE [
Name,School
Franck Damico,Berclay
Mark Brendanawicz,Harvard
Ramuh Leviathan,Centrale
Jake Peralta, NY Excelsior];


Table_2:
LEFT JOIN LOAD
     School,
     'Delete' AS Flag
INLINE [
School
Berclay
Harvard];


Final:
NOCONCATENATE LOAD
     *
RESIDENT Table_1
WHERE Flag <> 'Delete';
DROP TABLE Table_1;
DROP FIELD Flag FROM Final;

[ChennaiahNallani]

try like below

Table2:

LOAD

    School

FROM [lib://c/****.xlsx]

(ooxml, embedded labels, table is Sheet4);

Table1:

LOAD

    Name,

    School

FROM [lib://c/*****.xlsx]

(ooxml, embedded labels, table is Sheet3)

where not Exists(School);

Drop table Table2

[undergrinder]

Yeah, I mistyped the where condition

thank for inform me

tmp:
Load
    Name,
    School
From/Resident [table];

left join(tmp)
Load
    School
    1 as delete_flg
From/Resident [exclude table];

Final:
Load
    Name,
    School
Resident tmp
Where delete_flg<>1;  

drop table tmp;

I corrected the Where delete <> 1; expression;

G.