topic Re: How to filter out specific rows - Load QVD with a referenced condition in App Development

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

vsmejkal — Mon, 16 Apr 2018 10:10:51 GMT

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 — Mon, 16 Apr 2018 18:21:41 GMT

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;

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

vsmejkal — Tue, 17 Apr 2018 06:48:10 GMT

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

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

Lauri — Tue, 17 Apr 2018 16:21:59 GMT

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

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

vsmejkal — Wed, 18 Apr 2018 06:46:48 GMT

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

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

Lauri — Wed, 18 Apr 2018 14:54:12 GMT

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?

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

Qrishna — Wed, 18 Apr 2018 15:16:50 GMT

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.

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

Lauri — Wed, 18 Apr 2018 15:28:02 GMT

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;

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

vsmejkal — Thu, 19 Apr 2018 06:56:46 GMT

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

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

vsmejkal — Thu, 19 Apr 2018 06:57:56 GMT

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

Thank you and have a good day, VJ

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

Lauri — Thu, 19 Apr 2018 19:37:14 GMT

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.