Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
I am not able to do the advised way!
I have pasted my script could you guide me the possible way!
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
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.
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);
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.
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 .