-
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;
-
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
-
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...
-
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
-
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?
-
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;
-
-
-
-
-
-
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.
-
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
-
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
-
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.
-
-