4 Replies Latest reply: Feb 9, 2011 3:30 AM by Miguel Angel Baeyens de Arce RSS

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

    arun-cool

      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]

        • Can we write a SubQuery and  use Not in Key Word in QlikView
          Miguel Angel Baeyens de Arce

          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

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

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