Connecting two table and displaying only matched without joins.

[LP27]

Hi Everyone,

I need guidance for the below scenario -

I have got two tables and there is one common field in both the tables, but I want to display only matching values from common fields.

For Example : 

Table A:

ID	Name	Age
001	ABC	1
002	BCD	2
003	ZXY	2

Table B:

NO	Name	Age	Location
001	ABC	4
002	BCD	5
305	DEF	3
405	GHI	2
003	ZXY	2

As per the above tables- ID and NO are the two matching columns, Now i need to display in Table B only records which are present in Table A.

Output Table B : 

NO	Name	Age	Location
001	ABC	4
002	BCD	5
003	ZXY	2

As per my understanding, we can do it with Exists Condition. I dont want to join tables or Drop Tables.

Please let me know your views.

Thanks in Advance! 

[LP27]

I am not able to do the advised way! 

I have pasted my script could you guide me the possible way! 

[anushree1]

The issue here is the renaming of ID and No as code in both the places, try without aliasing ID and No Fields.

If alias is a must try using rename function post the exists 

[LP27]

I need aliasing because ID and No are columns that are common in both tables. I need to connect them.

Not sure how to use rename function post the Exist function.

could you give an example. 

[anushree1]

Try as below:

A:
LOAD
ID as Code,
Name as A_Name,
"Age" as A_Age
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @1);

B:
Load NO as Code,
Name,
"Age",
Location;
LOAD
"NO" ,
Name,
"Age",
Location
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @2)
where Exists(Code,NO);

[LP27]

Yes I have tried this method too.  This extracts All the records from field ID in Table A. 

I also tried using exist function in Table A too.  output was not matching my required Output  😞

Required Output: I just need matching values from  Field ID and No, keeping all the columns from both the table.

[anushree1]

Well I am not very sure of what you are trying to achieve here, another alternative for this issue could be the use of left keep plz check the below code:

A:
LOAD
ID as Code,
Name as A_Name,
"Age" as A_Age
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @1);

Left keep (A)
B:

LOAD
"NO" as Code,
Name,
"Age",
Location
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @2)
;

In case this also does not work request you to share the complete sample with the code so that I can check why zero rows are returned B .