Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
What rows does the SQL return? I'm unable to wrap my brain around how it works, even though I understand SQL...
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
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?
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.
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;
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
Hi again, guys thank you for your inputs. I give up.
Thank you and have a good day, VJ