topic Re: NOT IN in expression or using the variable in QlikView

NOT IN in expression or using the variable

gianluca_brick — Wed, 10 Aug 2022 11:37:05 GMT

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

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

Can anyone give me some advice?

Regards

Re: NOT IN in expression or using the variable

Or — Wed, 10 Aug 2022 11:59:40 GMT

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);

Re: NOT IN in expression or using the variable

gianluca_brick — Wed, 10 Aug 2022 12:14:42 GMT

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

Re: NOT IN in expression or using the variable

Or — Wed, 10 Aug 2022 12:47:58 GMT

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-data.htm

Re: NOT IN in expression or using the variable

gianluca_brick — Wed, 10 Aug 2022 12:50:46 GMT

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

Re: NOT IN in expression or using the variable

Or — Wed, 10 Aug 2022 12:56:19 GMT

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.

Re: NOT IN in expression or using the variable

gianluca_brick — Wed, 10 Aug 2022 13:29:08 GMT

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?

Re: NOT IN in expression or using the variable

Or — Wed, 10 Aug 2022 13:39:16 GMT

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.

Re: NOT IN in expression or using the variable

gianluca_brick — Wed, 10 Aug 2022 13:51:12 GMT

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