Listbox filter all anti join

[eclecticfish]

Hi im having a problem, im working on a qlikview where i need a listbox filter to flag something as YES or NO, normally I would do this by making a flag in the load script. Which i have also done here however i only have the "yes" part. since the flag is based on a script load where i first use sql to load in the main data and then let qlikview implicit join that table with other tables thorugh a common ID. like this LOAD * where exits(COMMON_ID)

The problem is that this flag is located in table loaded in this way, so the load will look like this 

LOAD * 

sql

select * from tableA;



and then 

LOAD * where exits(COMMON_ID)

sql 

select a.*,'YES' Falg from tableB b

however how do i make a listbox which select all record which did not exits on tableB and therefore dont have a FLAG ?

[MayilVahanan · Accepted solution]

HI @eclecticfish 

If you want result in front end, try like below

=Aggr(if(Len(Trim(Flag))=0, 'No', Flag), COMMON_ID)

If back end, try like below sample

Temp:
Load * Inline
[
COMMON_ID
1
2
3
4
];

Left Join
Load *, 'Yes' as Flag Inline
[
COMMON_ID
1
2
];

NoConcatenate
Load COMMON_ID, If(Flag='Yes', Flag, 'No') as Flag Resident Temp;

DROP Table Temp;

You can change inline with ursource.

[eclecticfish]

Just tried your front end solution and it works flawlessly, thank you!