topic Re: queries qlik sense in Connectivity & Data Prep

queries qlik sense

Mon, 27 Feb 2017 09:26:14 GMT

Hi everybody,

I want to ask you, if i can using function NOT EXISTS in qlik sens script with Sub-queries?
this is the example :

Villes:

LOAD VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

FROM [lib://Villes/Villes.xls]

(biff, embedded labels, table is Sheet1$);

Load VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

SELECT Villes

WHERE NOT EXISTS(VILLETIE)

(SELECT CPAYS, capitale

FROM Sheet1$

WHERE capitale = "OUI");

If 'yes' can you tell me where is the error in my queries?

Best regards.

souhail karoune

Re: queries qlik sense

cgdonders — Mon, 27 Feb 2017 10:50:57 GMT

the 'Where not exists' function only works with resident table. So first load the table Villes and then reload the data using resident load with Exists function.

Re: queries qlik sense

Mon, 27 Feb 2017 10:53:08 GMT

Villes:

LOAD VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

FROM [lib://Villes/Villes.xls]

(biff, embedded labels, table is Sheet1$);

Load VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

resident Villes

WHERE NOT EXISTS(Load CPAYS, capitale

FROM Sheet1$

WHERE capitale = "OUI");

Like that ?

Re: queries qlik sense

Mon, 27 Feb 2017 10:56:11 GMT

but he doesn't work also

Re: queries qlik sense

rahulpawarb — Mon, 27 Feb 2017 10:56:50 GMT

Hello Souhail,

If the second query is the SQL one then you can write the expected logic in SQL compliant way (Above written logic will not work).

Please share the application with sample data. This will help us to provide your expected response.

Regards!

Rahu

Re: queries qlik sense

cgdonders — Mon, 27 Feb 2017 10:57:31 GMT

No sorry, my bad. Like this

Villes:

LOAD VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

FROM [lib://Villes/Villes.xls]

(biff, embedded labels, table is Sheet1$);

Load

VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

RESIDENT Villes

WHERE NOT EXISTS(VILLETIE);

Re: queries qlik sense

cgdonders — Mon, 27 Feb 2017 11:00:17 GMT

or this:

Villes:

LOAD VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

FROM [lib://Villes/Villes.xls]

(biff, embedded labels, table is Sheet1$);

NewTableName:

Load

VILLETIE,

GeoKey02,

capitale,

CTIE,

LTIE,

CPAYS

RESIDENT Villes

WHERE NOT EXISTS(VILLETIE)

AND capitale = "OUI";

Re: queries qlik sense

Mon, 27 Feb 2017 11:03:13 GMT

merci beaucoup, thank you very much, is the better formula.

have a nice day

souhail.

Re: queries qlik sense

cgdonders — Mon, 27 Feb 2017 12:40:39 GMT

Please, close this question and mark it as Answered if you don't have further questions