Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (1)
1 Solution

Accepted Solutions
maxgro
Not applicable

Re: Exclude table lines based on other tables

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;

5 Replies
quwok
Not applicable

Re: Exclude table lines based on other tables

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
Not applicable

Re: Exclude table lines based on other tables

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

Re: Exclude table lines based on other tables

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
Not applicable

Re: Exclude table lines based on other tables

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

Re: Exclude table lines based on other tables

Thanks Massimo, this way was much more quickly!