topic Re: Where Not Exists not working in App Development

Where Not Exists not working

Lemmyduke1 — Tue, 28 Jan 2025 09:25:08 GMT

Hi everyone,

I have this code:

VendutoA:
LOAD

"Indirizzo 2" as "Vendere a - Indirizzo 2",
Indirizzo as "Vendere a - Indirizzo",
Città as "Vendere a - Città",
// '' as "Vendere a - Contatto",
"Cod. paese" as "Vendere a - Cod. paese/area geografica",
Provincia as "Vendere a - Prov.",
"Nome 2" as "Vendere a - Nome 2",
Nome as "Vendere a - Nome",
CAP as "Vendere a - CAP",
"Cod. regione" as "Vendere a - Regione",
Timestamp(Date("Data Inserimento", 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss.000000') as "Vendere a - Data Inserimento",
Timestamp(Date("Data Ultima Modifica", 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss.000000') as "Vendere a - Data Ultima Modifica",
"Nr." as §_DimVendutoA

FROM [$(vPath)St_Clienti.qvd]
(qvd);

GeoOutput_tmp:
LOAD Distinct
§_DimVendutoA as CheckPresenza
FROM [lib://GeoXXX:DataFiles/GeoOutput_*.csv] (txt, utf8, embedded labels, delimiter is ';');

GeoInput:
NoConcatenate
Load * Resident VendutoA
Where "Vendere a - Data Ultima Modifica">=Date(Today()-5) or "Vendere a - Data Inserimento">=Date(Today()-5) or
not Exists(CheckPresenza,§_DimVendutoA);

Drop Table VendutoA, GeoOutput;

exit script;

In Qlik Enterprise, GeoInput table produces an output of 923 lines, instead in Cloud I have 32059. If I eliminate "not Exists(CheckPresenza,§_DimVendutoA)", in Cloud I get 923 lines, the exact number of lines I'm expecting. Is there something different to consider in Cloud when doing this kind of function?
CheckPresenza and §_DimVendutoA are both the same, no spaces/blank characters, both strings.

Thanks,

Lemmy

Re: Where Not Exists not working

henrikalmen — Tue, 28 Jan 2025 09:59:37 GMT

Are you sure that the files you are loading contain the exact same data in your on-prem environment and in your saas environment?

Re: Where Not Exists not working

marcus_sommer — Thu, 30 Jan 2025 14:32:22 GMT

Make further sure that nothing has changed in regard to the load-order because exists() refers to already loaded field-values.

Re: Where Not Exists not working

Or — Thu, 30 Jan 2025 14:47:01 GMT

I think this one may be about the magic of not parenthesizing your OR statements and the Not Exists is a red herring.

Try:

Where ("Vendere a - Data Ultima Modifica">=Date(Today()-5) or "Vendere a - Data Inserimento">=Date(Today()-5)) or
not Exists(CheckPresenza,§_DimVendutoA);

Re: Where Not Exists not working

Lemmyduke1 — Thu, 30 Jan 2025 14:45:05 GMT

Thanks everyone, I was missing some files from prem environment.