Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

queries qlik sense

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

8 Replies
Partner
Partner

Re: queries qlik sense

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.

Highlighted
Not applicable

Re: queries qlik sense

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 ?

Highlighted
Not applicable

Re: queries qlik sense

but he doesn't work also

Highlighted
Valued Contributor III

Re: queries qlik sense

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

Highlighted
Partner
Partner

Re: queries qlik sense

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);

Highlighted
Partner
Partner

Re: queries qlik sense

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";

Highlighted
Not applicable

Re: queries qlik sense

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

have a nice day

souhail.

Highlighted
Partner
Partner

Re: queries qlik sense

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