topic Re: Connecting two table and displaying only matched without joins. in App Development

Connecting two table and displaying only matched without joins.

LP27 — Sat, 16 Nov 2024 03:13:27 GMT

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!

Re: Connecting two table and displaying only matched without joins.

tresB — Wed, 19 Feb 2020 07:56:29 GMT

Did you try with exists()? Like:

Load * from tableA;

Load * from tableB where Where Exists(ID, NO);

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 07:58:02 GMT

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!

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 08:08:15 GMT

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.

Re: Connecting two table and displaying only matched without joins.

anushree1 — Wed, 19 Feb 2020 08:41:05 GMT

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;

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 08:46:59 GMT

I tried as you say, Its generating zero values from Table B.

Re: Connecting two table and displaying only matched without joins.

anushree1 — Wed, 19 Feb 2020 08:48:18 GMT

Did you check the attachment i have shared

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 08:51:36 GMT

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.

Re: Connecting two table and displaying only matched without joins.

anushree1 — Wed, 19 Feb 2020 08:54:33 GMT

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

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 09:02:39 GMT

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.

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 12:23:29 GMT

I am not able to do the advised way!

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

Re: Connecting two table and displaying only matched without joins.

anushree1 — Wed, 19 Feb 2020 14:19:45 GMT

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

Re: Connecting two table and displaying only matched without joins.

LP27 — Wed, 19 Feb 2020 15:25:06 GMT

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.

Re: Connecting two table and displaying only matched without joins.

anushree1 — Thu, 20 Feb 2020 06:31:41 GMT

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);

Re: Connecting two table and displaying only matched without joins.

LP27 — Thu, 20 Feb 2020 07:43:59 GMT

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.

Re: Connecting two table and displaying only matched without joins.

anushree1 — Thu, 20 Feb 2020 08:08:11 GMT

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 .