Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

vsmejkal
New Contributor II

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

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

Tags (1)
10 Replies
laurischarf
Contributor II

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

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;

vsmejkal
New Contributor II

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

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

laurischarf
Contributor II

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

What rows does the SQL return? I'm unable to wrap my brain around how it works, even though I understand SQL...

vsmejkal
New Contributor II

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

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

laurischarf
Contributor II

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

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?

krishna_2644
Valued Contributor III

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

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.

laurischarf
Contributor II

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

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;

vsmejkal
New Contributor II

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

Hi Lauri

Your unusual solution looked promising. But for some reason it is not filtering up. It returns the same number of rows.

Capture.JPG

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 thereSmiley Surprised(

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

vsmejkal
New Contributor II

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

Hi again, guys thank you for your inputs. I give up.

Thank you and have a good day, VJ

Community Browser