NOT IN in expression or using the variable

[gianluca_brick]

Hi,
i have a sql query which needs to filter the data with the possible values ​​of a subquery with not in clause. I'll explain better Table A with MemberA field, Table B with MemberB and Data field filtered by a dynamic filter with the use of a calendar.

ES.

TABLE A:

MemberA  

1        

2

3

TABLE B filter with calendar ='26/02/2022':

MemberB   Data

2        26/02/2022

3        26/02/2022

Now I want to have all records from table A that don't exist in B with calendar selection='26/02/2022'

Table A result:

MemberA

1

Can anyone give me some advice?

Regards

[Or]

If 26/02/2022 is a selection made in the app itself, the only reasonable way to achieve this is using On Demand App Generation (ODAG).

If this is something that is fixed / hardcoded, you could use something like:

Load MemberB, Data

From TableB

WHERE Data = '26/02/2022' ;

Load * From TableA Where Not Exists(MemberB, MemberA);

[gianluca_brick]

No I can't because I load all the data from the two tables and then dynamically execute the filter on table B. on table B I can filter for various dates

[Or]

As I said, if this filter needs to be dynamic, and it needs to be in a query, you'll need to use ODAG. See: https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/DataSource/Manage-big-dat...

[gianluca_brick]

Ok I try to see ODAG. But does it also work on qlikView?

[Or]

Unfortunately, ODAG is not available in QlikView, just Qlik Sense. I didn't catch that this was in the QV section. With QV, you could probably do something using a macro to either reload the current app with a variable controlling the date. 

Note that if this doesn't have to be at the query level, you could achieve this quite easily if the tables were actually linked (rename the Member field so that both tables share the same column name), by selecting the desired date and then applying "Select Excluded" to the Member field.

[gianluca_brick]

i created the join with the tables using the common name Member, but unfortunately i never used the qlikview macros. Are there any work around for this?

[Or]

If you joined the tables, you don't need to use macros - just select the date and then apply "Select Excluded" to the Member field so select all IDs that do *not* have a record for that date (and the rest of your selection, if any applies).

You could also have a look at using an expression along the lines of

Only({< Member = e(Data) >} ID)

Not sure if that specifically will work but you can have a look at using e() in set analysis as a solution.

[gianluca_brick]

hi, it works fine.
There is a way, using of a variable, to automate apply "Select Excluded"?