Using Where NOT Exists

[eduardo_dimperio]

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?

[Anonymous]

try WHERE NOT Exists('OID_METER');

[maxgro]

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

[maxgro]

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_dimperio]

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

[eduardo_dimperio · Accepted solution]

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_dimperio]

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

[Anonymous]

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.