Filtering innerjoined table

[su_pyae]

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. 

[stevejoyce]

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.

[MayilVahanan]

Hi

Try like below

Load fields resident AlreadyLoadedtablename where condition;

ex:

Load CustomerID resident CustomerTable where CustomerType = 'Member';

[su_pyae]

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. 

[stevejoyce · Accepted solution]

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;

[su_pyae]

Thank you!

It works now 🙂

[NadiaB]

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,

[su_pyae]

Thank you so much! It was such an interesting read.