Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Using Where NOT Exists

Hi,

I have this situation:

vCarga = Date( Today(), 'YYYYMMDD' );

DESCONEXAO:

LOAD

*

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Periodo_de_Desconexao_$(vCarga).qvd](qvd);

NoConcatenate

Outer_Table:

LOAD

*

Resident RS_SYSTEM;

After Load i get an OID_METER = 80705 (and many others) from Outer_Table, this OID_METER is not present in DESCONEXAO table.

So if i want only the OID_METER's from Outer_Table that is not in DESCONEXAO i try this:

DESCONEXAO:

LOAD

*

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Periodo_de_Desconexao_$(vCarga).qvd](qvd);

NoConcatenate

Outer_Table:

LOAD

*

Resident RS_SYSTEM

WHERE NOT Exists(OID_METER);

but now my Outer_Table return empty.

Where im wrong please?

Outer_Table.JPG

Outer_Table_withnotexist.JPG

1 Solution

Accepted Solutions
eduardo_dimperi
Valued Contributor II

Re: Using Where NOT Exists

Guys, I solve the problem,i create a qvd to RS_SYSTEM

DESCONEXAO:

LOAD

*

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Periodo_de_Desconexao_$(vCarga).qvd](qvd);

Outer_Table:

LOAD

*

FROM [lib://Dados/MI/Leituras/Consumo/Condominios/RS_UNIFICADO$(vCarga).qvd](qvd)

WHERE NOT Exists(OID_METER);

7 Replies
roharoha
Valued Contributor III

Re: Using Where NOT Exists

try WHERE NOT Exists('OID_METER');

MVP
MVP

Re: Using Where NOT Exists

NoConcatenate

Outer_Table:

LOAD

*

Resident RS_SYSTEM

WHERE NOT Exists(OID_METER);

but now my Outer_Table return empty.

You load from a resident table, RS_SYSTEM

In RS_SYSTEM you already have all the values of OID_METER

All the OID_METER values exist

The NOT exists doesn't return any records

MVP
MVP

Re: Using Where NOT Exists

maybe something like this

RS_SYSTEM:

load *, 1 as FIELD1 inline [

OID_METER

1

2

3

4

];

DESCONEXAO:

LOAD *, OID_METER as OID_METER_TMP inline [

OID_METER

2

3

];

// only load values 1 4 from RS_SYSTEM

Outer_Table:

NOCONCATENATE

LOAD OID_METER, 2 as FIELD2

RESIDENT RS_SYSTEM

WHERE not EXISTS (OID_METER_TMP, OID_METER)

;

DROP Field OID_METER_TMP;

eduardo_dimperi
Valued Contributor II

Re: Using Where NOT Exists

Hi Massimo, i thought about it, but since i had loaded DESCONEXAO after RS_SYSTEM i imagined that will replace RS SYSTEM.

eduardo_dimperi
Valued Contributor II

Re: Using Where NOT Exists

Guys, I solve the problem,i create a qvd to RS_SYSTEM

DESCONEXAO:

LOAD

*

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Periodo_de_Desconexao_$(vCarga).qvd](qvd);

Outer_Table:

LOAD

*

FROM [lib://Dados/MI/Leituras/Consumo/Condominios/RS_UNIFICADO$(vCarga).qvd](qvd)

WHERE NOT Exists(OID_METER);

eduardo_dimperi
Valued Contributor II

Re: Using Where NOT Exists

Hi Robin, the single quote we use (as i know), when you want a value or a string.

Like here:

Exists(Employee, 'Bill')

For me i need to check all OID_METER's

but thank you any way

roharoha
Valued Contributor III

Re: Using Where NOT Exists

Basically you're right, but have a look at this thread by rwunderlich‌.

https://community.qlik.com/thread/241280

It is in combination with if exists(), but I thought this could be your problem.