Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 ?
but he doesn't work also
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
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);
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";
merci beaucoup, thank you very much, is the better formula.
have a nice day
souhail.
Please, close this question and mark it as Answered if you don't have further questions