Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I hope you guys are having a great day.
I want to filter the table based on some columns (at least 1 and at most 2) and my table is a result from inner-joining two tables.
I know how to filter from the source with WHERE clause but I am having trouble filtering from the already loaded table into a new table.
If you load a table in qlik with all same field names as another table it automatically concatenates/unions them. So in your case when you create New_Table, it's actually concatenating that to IJ, and then you end up dropping IJ so you dont have either table.
To avoid auto-concatenate you can use NOCONCATENATE like below:
New_Table:
NOCONCATENATE LOAD *
Resident IJ WHERE ( Customer_ID = 'this' or Customer_ID = 'that');
DROP TABLE IJ;
Can you post the code you have and show what you are trying to do? I don't follow why you can't do a where clause from an already loaded table into new table.
Hi
Try like below
Load fields resident AlreadyLoadedtablename where condition;
ex:
Load CustomerID resident CustomerTable where CustomerType = 'Member';
Sorry about the late replies.
I have tried it that way.
I have a table called IJ which is an inner-joined table and I want to filter out the rows based on 'Customer_ID' column.
IJ:
LOAD * FROM DS1;
Inner Join (IJ)
LOAD * FROM DS2;
New_Table:
LOAD *
Resident IJ WHERE ( Customer_ID = 'this' or Customer_ID = 'that');
DROP TABLE IJ;
If I used the above script, data load process end without errors but I won't be able to see the table in Data Model Viewer anymore, saying "Access Not Allowed".
If I didn't put in "Drop Table IJ" in the script, it will go through without errors but in the Data Model Viewer, I can only see IJ Table. My New_Table doesn't get loaded at all.
I am sorry that my question was a bit vague.
If you load a table in qlik with all same field names as another table it automatically concatenates/unions them. So in your case when you create New_Table, it's actually concatenating that to IJ, and then you end up dropping IJ so you dont have either table.
To avoid auto-concatenate you can use NOCONCATENATE like below:
New_Table:
NOCONCATENATE LOAD *
Resident IJ WHERE ( Customer_ID = 'this' or Customer_ID = 'that');
DROP TABLE IJ;
Thank you!
It works now 🙂
Just to keep in mind there are other confiserations like performance, EXISTS and LEFT KEEP can be used, please refer to the following post where Henric_Cronström explains some interesting technical details.
https://community.qlik.com/t5/QlikView-App-Dev/Left-Keep-vs-Where-Exists/m-p/448442
Kind Regards,
Thank you so much! It was such an interesting read.