topic Re: Listbox filter all anti join in QlikView

Listbox filter all anti join

eclecticfish — Sat, 16 Nov 2024 17:46:19 GMT

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 ?

Re: Listbox filter all anti join

MayilVahanan — Fri, 27 Nov 2020 01:14:35 GMT

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.

Re: Listbox filter all anti join

eclecticfish — Fri, 27 Nov 2020 07:55:05 GMT

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