Can we write a SubQuery and use Not in Key Word in QlikView

[#1]

Hi Friends,

I have a Scenario ,wherei need to Write a Query and in that i need to write a Sub Query by using not in ,is there is any way i can write this Query in Qlik View.

eg:select * from Emp where Empid not in (Select * from tbl_resignation)\

Thanks in Advance

Arun..[:D]

[Miguel_Angel_Baeyens · Accepted solution]

Hello Arun,

I'd do as follows:

NotToBeLoaded: LOAD Empid; SQL SELECT Empid FROM tbl_resignation; Data: LOAD * WHERE NOT EXISTS(Empid); // In case table Emp has already a field called "Empid" SQL SELECT * FROM Emp; DROP TABLE NotToBeLoaded;

Hope that helps

[#3]

Miguel Angel ,

thank u for ur response, Can u Please Tell me how emp table gets related ,they seem to be aidependent stmt.

LOAD *
WHERE NOT EXISTS(Empid); // In case table Emp has already a field called "Empid"
SQL SELECT *
FROM Emp;

[suniljain]

Full load required to generate qvd initially)

DIRECTORY;

[VBAP - Order Items]:

LOAD

VBELN & '/'& POSNR as [%VBELN/POSNR],

[MEINS] as [Base Unit_MEINS],

[MATKL] as [Material Group_MATKL],

[WERKS] as [Plant_WERKS],

[MATNR] as [Material_MATNR],

[SPART] as [Division_SPART],

[KPEIN] as [Pricing unit_KPEIN],

[KMEIN] as [Unit of measure_KMEIN],

[ERNAM] as [Created by_ERNAM],

[ERDAT] as [Created on_ERDAT],

[AEDAT] as [Changed on_AEDAT],

[POSNV] as [Item_POSNV],

[VSTEL] as [Shipping Point_VSTEL],

[VBELN] as [Sales Document_VBELN],

[POSNR] as [Item_POSNR],

[NETWR] as [Net value_NETWR];

SQL SELECT MEINS MATKL WERKS MATNR SPART KPEIN KMEIN ERNAM ERDAT AEDAT POSNV VSTEL VBELN NETWR POSNR

FROM VBAP

WHERE VBELN IN (

SELECT VBELN FROM VBAK // Order Header

WHERE ERDAT > '$(vLastReload)'

OR AEDAT > '$(vLastReload)'

);

CONCATENATE ([VBAP - Order Items])

LOAD *

FROM VBAP.qvd (qvd)

WHERE not exists ([%VBELN/POSNR]);

[Miguel_Angel_Baeyens]

Hello Arun,

QlikView's EXISTS() allows to check for a field already loaded.

In this case, you first load all those "Empid" you don't want to load. Then, you load all data except for those records where "Empid" equals to the ones already loaded. Third, you drop the table containing only the "Empid" not to load.

Take the following example:

DontLoad:LOAD * INLINE [EmpidABC]; Data:LOAD *FROM File.qvd (qvd)WHERE NOT EXISTS(Empid); DROP TABLE DontLoad;

This will load all records in File.qvd but those whose "Empid" has values "A", "B" or "C".

Hope that helps.