Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
cgdonders
Partner - Creator
Partner - Creator

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.

Not applicable
Author

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 ?

Not applicable
Author

but he doesn't work also

rahulpawarb
Specialist III
Specialist III

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

cgdonders
Partner - Creator
Partner - Creator

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

cgdonders
Partner - Creator
Partner - Creator

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

Not applicable
Author

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

have a nice day

souhail.

cgdonders
Partner - Creator
Partner - Creator

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