Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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
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?
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;
Thanks Massimo, this way was much more quickly!