10 Replies Latest reply: Apr 19, 2018 3:37 PM by Lauri Scharf RSS

    How to filter out specific rows - Load QVD with a referenced condition

    V.J. Smejkal

      Dear all

       

      Please could you help me re-write the following SQL into a Qlik Load syntax? The trouble I cannot connect to the database directly so I cannot use the SQL as it is. I only have a QVD available. The idea is to filter-out specific rows.

       

      SQL(Working well):

      SELECT

      UserName,

      Path,

      AccessType,

      SecurityGroupName


         FROM server.schema.DB_table FUA1

         WHERE NOT EXISTS

            (

            SELECT

            *

               FROM server.schema.DB_table

               WHERE UserName = FUA1.UserName AND Path = FUA1.Path

            )



      My wrong attempt No.1(Error: UserName not found):

      [A_All]:

      Load

         UserName AS A_UserName,

         Path AS A_Path,

         AccessType AS A_AccessType,

         SecurityGroupName AS A_SecurityGroupName

            FROM [lib://Connection_L1/DB_table.qvd](qvd);

         

      [B_Sub]:

      Load

         UserName,

         Path,

         AccessType,

         SecurityGroupName

            Resident [A_All]

               WHERE UserName = A_UserName;



      My wrong attempt No.2 (Not working altogether - wrong sub load syntax I'd have thought)

      [A_All]:

      Load

      UserName AS A_UserName,

         Path AS A_Path,

         AccessType AS A_AccessType,

         SecurityGroupName AS A_SecurityGroupName

            FROM [lib://Connection_L1/DB_table.qvd](qvd);


           Where Not Exists(

              (Load

                 *

                 FROM [lib://Connection_L1/DB_table.qvd](qvd);

               )

            )


      Thank you, VJ