Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Exclude table lines based on other tables

Hi Guys!

I have two tables like the examples below. I need to exclude the IDs from table 2 that do not belong to the table 1, after already load this 2 tables. Table 1 i get from SharePoint and table 2 from a SQL server.

Table 1

IDDate
ABC101/03/2015
ABC204/05/2014
ABC303/05/2014
ABC420/06/2014
ABC515/09/2014

Table 2

IDDate
ABC115/09/2014
ABC120/06/2014
ABC503/05/2014
ABC2004/05/2014
ABC3001/03/2015
ABC3504/05/2014

So i expect to have the result below in the table 2, considering that my ID in table 2 can repeat:

IDDate
ABC115/09/2014
ABC120/06/2014
ABC503/05/2014

Can you please suggest a solution?

BR//Marcelo Fonseca

1 Solution

Accepted Solutions
maxgro
MVP
MVP

t1:

LOAD ID, Date as Date1

FROM

[https://community.qlik.com/thread/162043]

(html, codepage is 1252, embedded labels, table is @1);

t2:

NoConcatenate

LOAD ID, Date as Date2

FROM

[https://community.qlik.com/thread/162043]

(html, codepage is 1252, embedded labels, table is @2);

Right Keep (t2)

load ID Resident t1;

View solution in original post

5 Replies
quwok
Creator III
Creator III

I would suggest the Exists() function. So something like:

Table1:

LOAD Table1_ID,

Table1_Date

FROM SharePoint file;

Table2:

LOAD Table2_ID,

Table2_Date

FROM SQL Table

WHERE EXISTS(Table1_ID, Table2_ID);

Hope this will guide you on the right track.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

If you load your second table from a SQL server using a SQL SELECT, and your first (SharePoint) table LOAD comes before, then do this:

First in the SharePoint LOAD, create a duplicate of the ID column and call it TempID. YMMV as I do not really know how you load information from SharePoint.

[Table 1]:

LOAD...

        ID AS TempID,

        ...

Then add a simple Preceding LOAD in front of the SQL SELECT for Table 2 like this:

[Table 2]:

LOAD * WHERE Exists(TempID, ID);

SQL SELECT ...;

Finally drop the TempID field using a statement like this:

DROP Field TempID;

That should do it.

Best,

Peter

Not applicable
Author

Thanks for the suggestions, Use the Preceding LOAD (Where Exists) in front of the SQL SELECT worked! But the problem is, filter all these IDs while loading from SQL took more time than load all the registers from the database (I have more than 40.000 unique IDs). I Would like to exclude the Orphaned ID in table 2 just after load all the table 2. Load a copy of the table 2 with the Where Exist(TempID, ID) and drop table 2 i think could be a solution, but there is another one more efficient way?

maxgro
MVP
MVP

t1:

LOAD ID, Date as Date1

FROM

[https://community.qlik.com/thread/162043]

(html, codepage is 1252, embedded labels, table is @1);

t2:

NoConcatenate

LOAD ID, Date as Date2

FROM

[https://community.qlik.com/thread/162043]

(html, codepage is 1252, embedded labels, table is @2);

Right Keep (t2)

load ID Resident t1;

Not applicable
Author

Thanks Massimo, this way was much more quickly!