Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
vincent_bellang
New Contributor III

Reverse inner keep

Greetings everyone,

I have 2 tables like those:

Name

School

Franck DamicoBerclay
Mark BrendanawiczHarvard
Ramuh LeviathanCentrale
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

NameSchool
Ramuh LeviathanCentrale
Jake PeraltaNY 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!

Tags (1)
1 Solution

Accepted Solutions
Luminary
Luminary

Re: Reverse inner keep

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;

Capture.PNG

4 Replies
undergrinder
Valued Contributor II

Re: Reverse inner keep

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.

Luminary
Luminary

Re: Reverse inner keep

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;

Capture.PNG

ChennaiahNallani
Contributor III

Re: Reverse inner keep

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
Valued Contributor II

Re: Reverse inner keep

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.