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

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

V.J. Smejkal Apr 16, 2018 6:10 AM

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

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

Lauri Scharf Apr 16, 2018 2:21 PM (in response to V.J. Smejkal)

Your first attempt fails because you have renamed UserName, so the field doesn't exist with that name anymore.

I may be missing something with your SQL, but aren't you just excluding all rows where UserName = FUA1.UserName AND Path = FUA1.Path?

If so, then:

[A_All]:
Load
UserName,
Path,
AccessType,
SecurityGroupName
FROM [lib://Connection_L1/DB_table.qvd](qvd)
Where UserName <> FUA1.UserName AND Path <> FUA1.Path;
Like Show 0 Likes (0)

Actions
- Re: How to filter out specific rows - Load QVD with a referenced condition
  
  V.J. Smejkal Apr 17, 2018 2:48 AM (in response to Lauri Scharf)
  
  Dear Lauri
  
  Thank you for your reply. I may have over simplified the SQL to express the idea. This is a full/better form of the sample:
  
  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 AND AccessType = 'write' AND FUA1.AccessType = 'write'
        )
  
  I checked your reply but the trouble is the 'FUA1' is an SQL table alias referencing to the table. There is no such thing in Qlik. I have spent many days trying to find the solution.
  
  The best line would be: How to do a conditional table join
  
  Thank you, VJ
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How to filter out specific rows - Load QVD with a referenced condition
    
    Lauri Scharf Apr 17, 2018 12:21 PM (in response to V.J. Smejkal)
    
    What rows does the SQL return? I'm unable to wrap my brain around how it works, even though I understand SQL...
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How to filter out specific rows - Load QVD with a referenced condition
      
      V.J. Smejkal Apr 18, 2018 2:46 AM (in response to Lauri Scharf)
      
      Hi Lauri
      
      It filters out the rows where the user access of read overlaps the write. The idea behind is to remove the read access because if you have the write the read comes without saying. In terms of the rows in my case it's roughly 5 000 000 rows reduced to 4 500 000 rows + much better data legibility.
      
      Thank you, VJ
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: How to filter out specific rows - Load QVD with a referenced condition
        
        Lauri Scharf Apr 18, 2018 10:54 AM (in response to V.J. Smejkal)
        
        Hi VJ,
        Hopefully I understand correctly: You want to remove the rows where AccessType='read' but only for users who also have 'write' rows. So if a user has only a 'read' row, keep it?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to filter out specific rows - Load QVD with a referenced condition
        
        Lauri Scharf Apr 18, 2018 11:28 AM (in response to Lauri Scharf)
        
        If I understand correctly, this should work:
        
        AllRows:
        Load
        UserName,
        Path,
        AccessType as AT,
        SecurityGroupName;
        //Replace this test sample with your SQL Select after testing:
        Load * inline [
        UserName, Path, AccessType, SecurityGroupName
        VJ, c:\crypto, read, admin
        VJ, c:\crypto, write, admin
        LS, c:\public, read, user
        ];
        /* Real data:
        SQL SELECT
        UserName,
        Path,
        AccessType,
        SecurityGroupName
        FROM server.schema.DB_table
        */
        SomeRows:
        LOAD
        UserName,
        Path,
        SecurityGroupName,
        MaxString(AT) as AccessType
        Resident AllRows
        Group By
        UserName,
        Path,
        SecurityGroupName;
        
        drop table AllRows;
        
        Like Show 0 Likes (0)
        
        Actions
Re: How to filter out specific rows - Load QVD with a referenced condition

Krishna Nagulapally Apr 18, 2018 11:16 AM (in response to V.J. Smejkal)

Try this -

LoadingNotExistsValues1st:
Load
FIELD1,
*
FROM server.schema.DB_table
WHERE UserName = FUA1.UserName AND Path = FUA1.Path;

LoadingDataForTheValuesThatDoNotExistsInAboveTable:
Load
FIELD2,
*
FROM server.schema.DB_table FUA1
where NOT EXISTS (FIELD1, FIELD2);

Where Field2 and field1 are look up fields from the respective tables.
Like Show 0 Likes (0)

Actions
- Re: How to filter out specific rows - Load QVD with a referenced condition
  
  V.J. Smejkal Apr 19, 2018 2:56 AM (in response to Krishna Nagulapally)
  
  Hi Lauri
  
  Your unusual solution looked promising. But for some reason it is not filtering up. It returns the same number of rows.
  
  So I removed the SecurityGroupName from the GROUP and LOAD, but the result is filtering out too many records. Something is wrong. Though at least it is working. I have to admin I give up on Qlik. I'll return back to the good old SQL and do all the data manipulation in there:o(
  
  AllRows:
  Load
  UserName,
  Path,
  AccessType as AT,
  SecurityGroupName
  FROM [lib://CNSBG_Global_ARES_public_L1/IT_FolderUserAccess.qvd](qvd);
  
  SomeRows:
  LOAD
  UserName,
  Path,
  //SecurityGroupName,
  MaxString(AT) as AccessType
  Resident AllRows
  Group By
  UserName,
  Path;
  
  drop table AllRows;
  
  Exit script;
  
  It reduces the rows from 4 955 850 to 3 990 000. But it should be 4 502 431. It seems the MaxString is grouping up too many records.
  
  Thank you, VJ
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How to filter out specific rows - Load QVD with a referenced condition
    
    V.J. Smejkal Apr 19, 2018 2:57 AM (in response to V.J. Smejkal)
    
    Hi again, guys thank you for your inputs. I give up.
    
    Thank you and have a good day, VJ
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: How to filter out specific rows - Load QVD with a referenced condition
    
    Lauri Scharf Apr 19, 2018 3:37 PM (in response to V.J. Smejkal)
    
    Don't be fooled by the row count that the data load log shows you. It processed 4 955 850 rows, but that doesn't mean that's what ended up in the table. Did you look at the actual number of rows after it finished?
    
    Removing SecurityGroupName from the Group By *should* result in fewer rows (if users can have multiple different values for SecurityGroupName), so that makes sense to me.
    
    But at the end of the day, sometimes SQL is the way to go... just make note of the performance differences between Qlik and your SQL program. In some queries, Qlik can be MUCH faster.
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post