Struggling to create table resembling SELECT NOT IN (...) in SQL

[nicolaslopedebarrios]

Hi, happy New Year everybody.

I've been trying for a few days to create a table in Qlik Sense Enterprise Nov 2024 from two existing tables in a database. One has fuel purchases made with cards on certain dates, locations, etc. and the other is the cards master table, which contains all cards of an organisation. I would like to create a table with only the card numbers that haven't been used in the past 6 months.

I tried this:

// Create tarjetas master table
LOAD 
	NUMERO_DE_TARJETA, 
	TIPO_DE_IDENTIFICACION, 
	IDENTIFICACION, 
	ESTADO, 
	BLOQUEADA;
[TARJETAS]:
SELECT "NUMERO_DE_TARJETA",
	"TIPO_DE_IDENTIFICACION",
	"IDENTIFICACION",
	"ESTADO",
	"BLOQUEADA"
FROM "DB"."TARJETAS";

// Load purchases from last 6 months:
[TARJETAS_CONSUMOS_6_MESES]:
NoConcatenate 
LOAD DISTINCT
    NUMERO_DE_TARJETA
RESIDENT [CONSUMOS_FLOTA]
WHERE FECHA >= AddMonths(MonthStart(Today()), -6);

// Cards with no purchases in the last 6 months
[TARJETAS_SIN_CONSUMOS_6_MESES]:
NoConcatenate 
LOAD NUMERO_DE_TARJETA, 
TIPO_DE_IDENTIFICACION AS TIPO_DE_IDENTIFICACION_SC, 
IDENTIFICACION AS IDENTIFICACION_SC, 
ESTADO AS ESTADO_SC, 
BLOQUEADA AS BLOQUEADA_SC, 
NRO_CONTRATO AS NRO_CONTRATO_SC, 
CONTRATO_DESC AS CONTRATO_DESC_SC 
RESIDENT [TARJETAS] 
WHERE NOT EXISTS (NUMERO_DE_TARJETA);

Don't know why [TARJETAS_SIN_CONSUMOS_6_MESES] is empty.

I tried to follow Example 4 in https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

This is easily done in SQL with a subquery in the where clause 

(SELECT numero_tarjeta FROM tarjetas WHERE numero_tarjeta NOT IN (Select numero_tarjeta FROM consumos WHERE fecha >= 6month_date));

[Clement15]

Hello,

The where not exist looks at the entire application to make this selection.
I advise you to retrieve the desired id and then do a join on this table.
In this idea :

[TARJETAS_SIN_CONSUMOS_6_MESES]:
NoConcatenate 
LOAD DISTINCT
    NUMERO_DE_TARJETA
RESIDENT [CONSUMOS_FLOTA]
WHERE FECHA <= AddMonths(MonthStart(Today()), -6);


left join 
LOAD NUMERO_DE_TARJETA, 
TIPO_DE_IDENTIFICACION AS TIPO_DE_IDENTIFICACION_SC, 
IDENTIFICACION AS IDENTIFICACION_SC, 
ESTADO AS ESTADO_SC, 
BLOQUEADA AS BLOQUEADA_SC, 
NRO_CONTRATO AS NRO_CONTRATO_SC, 
CONTRATO_DESC AS CONTRATO_DESC_SC 
RESIDENT [TARJETAS] ;

[nicolaslopedebarrios]

Hi @Clement15 , your suggested LEFT JOIN doesn't work, it brings all records from  [CONSUMOS_FLOTA].

Having which cards had been used in [TARJETAS_CONSUMOS_6_MESES], I need to extract from [TARJETAS] those that are not in that table. 

[Daniel_Pilla · Accepted solution]

While you can do this logic in Qlik syntax, I just want to note that you can also execute your SQL subquery from Qlik as well. It will push it down and execute it at the DB. There are times when that is desirable when data volumes are too large to manipulate in memory.

As for your issue above, where not exists() looks at all field values loaded for the field in question previously anywhere in the Qlik application. It is common to alias the field and then use exists()'s second argument. See examples on this here.

[Clement15]

Indeed I made a mistake, this should be more consistent with your need

[TARJETAS_SIN_CONSUMOS_6_MESES]:
NoConcatenate 
LOAD DISTINCT
NUMERO_DE_TARJETA
max(FECHA)
RESIDENT [CONSUMOS_FLOTA]
WHERE max(FECHA) <= AddMonths(MonthStart(Today()), -6)
group by NUMERO_DE_TARJETA;


left join 
LOAD NUMERO_DE_TARJETA, 
TIPO_DE_IDENTIFICACION AS TIPO_DE_IDENTIFICACION_SC, 
IDENTIFICACION AS IDENTIFICACION_SC, 
ESTADO AS ESTADO_SC, 
BLOQUEADA AS BLOQUEADA_SC, 
NRO_CONTRATO AS NRO_CONTRATO_SC, 
CONTRATO_DESC AS CONTRATO_DESC_SC 
RESIDENT [TARJETAS] ;