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!
Did you try with exists()? Like:
Load * from tableA;
Load * from tableB where Where Exists(ID, NO);
Yes I tried like this,:
Table B:
No as Code,
Name,
Age,
Location
From(path);
Table A:
Load
ID as code,
Name,
Age,
from (Path)
Where exists(Code,ID);
Was not successful with the results!
Thanks for reply!
If I try the way you say, it generating Zero values from Tables B and Generating all the values from Table B.
You must use the condition : Where exists(ID,Code);
Please use the script as below and check it must work, also attaching the sample for reference:
A:
LOAD
ID,
Name,
"Age"
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @1);
B:
LOAD
"NO",
Name,
"Age",
Location
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @2)
where Exists( ID,NO);
Drop table A;
I tried as you say, Its generating zero values from Table B.
Did you check the attachment i have shared
Yes I did, But there you have dropped Table A, but I would needing other columns from Table A so Wont be able to drop.
if you need Table A you can keep it too, I dropped it for removing Synthetic keys, so if you want Table A jus rename Fields Name and Age in Table A and you must be good to go
So this is my script-
Table A:
LOAD
ID as Code,
Name,
"Type",
"PM Code",
applymap('P_Map', "PM Code",'Other') as "PM",
applymap('BMap', "PM Code",'Other') as "BU",
applymap('SMap', "PM Code",'Other') as "Seg",
FROM [Path];
Table B:
LOAD
No as Code,
"Part or Series #",
"Program 1",
Notes,
"Function Category"
FROM [Path]
where Exists(ID,NO);
This is my original script! not sure how to proceed with this scrip here.