topic Re: Exclude table lines based on other tables in QlikView

Exclude table lines based on other tables

Tue, 28 Apr 2015 14:52:40 GMT

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

ID	Date
ABC1	01/03/2015
ABC2	04/05/2014
ABC3	03/05/2014
ABC4	20/06/2014
ABC5	15/09/2014

Table 2

ID	Date
ABC1	15/09/2014
ABC1	20/06/2014
ABC5	03/05/2014
ABC20	04/05/2014
ABC30	01/03/2015
ABC35	04/05/2014

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

ID	Date
ABC1	15/09/2014
ABC1	20/06/2014
ABC5	03/05/2014

Can you please suggest a solution?

BR//Marcelo Fonseca

Re: Exclude table lines based on other tables

quwok — Tue, 28 Apr 2015 15:04:38 GMT

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.

Re: Exclude table lines based on other tables

Peter_Cammaert — Tue, 28 Apr 2015 15:05:25 GMT

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

Re: Exclude table lines based on other tables

Wed, 29 Apr 2015 19:05:28 GMT

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?

Re: Exclude table lines based on other tables

maxgro — Wed, 29 Apr 2015 19:18:59 GMT

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;

Re: Exclude table lines based on other tables

Wed, 29 Apr 2015 21:32:49 GMT

Thanks Massimo, this way was much more quickly!