Join 2 tables with different columns

[Tuannna1 (Tuan Anh)]

Hello everyone,
I have 2 strings as follows:

Name = "Alex, Jackson, John, Ben, Ron"
ID = "85012, 85013, 85014, 85015, 85016"

=>
|Name ID |
|Alex, 85012|
|Jackson, 85013|
|…, …|

I do a comma-separated string and put it in array.

Then, I use [Collection to DataTable] to convert them to DataTable.

I get 2 DataTable , I want to join them together. Is there any way to do it?

Can you help me?
Thanks in advance,

[mkankatala (Mahesh Kankatala)]

Hi @Tuannna1

You can use the Join data tables activity to join the two datatables.

There is type of joins in Join datatable activity

1. Inner - Keep all rows from DataTable1 and DataTable2 which meet the Join rule. Any rows that do not meet the rule are removed from the resulting table.
2. Left - Keep all rows from DataTable1 and only the values from DataTable2 which meet the Join rule. Null values are inserted into the column for the rows from DataTable1 that don’t have a match in the DataTable2 rows.
3. Full - Keep all rows from DataTable1 and DataTable2, regardless of whether the join condition is met. Null values are added into the rows from both tables that don’t have a match.

Hope it helps!!

[Rahul_Unnikrishnan]

Hello @Tuannna1

You can use Text to Datatable activity and convert string to Datatable. Then you can use Join datatable activity to achieve this requirement.

Thanks

[Tuannna1 (Tuan Anh)]

@mkankatala

• Inner - Keep all rows from DataTable1 and DataTable2 which meet the Join rule. Any rows that do not meet the rule are removed from the resulting table.
• Left - Keep all rows from DataTable1 and only the values from DataTable2 which meet the Join rule. Null values are inserted into the column for the rows from DataTable1 that don’t have a match in the DataTable2 rows.
• Full - Keep all rows from DataTable1 and DataTable2, regardless of whether the join condition is met. Null values are added into the rows from both tables that don’t have a match.

Hi,
@mkankatala

Because the values from DataTable1 and DataTable2 do not meet the rule. So I use concatenation type:
Full - Keeps all rows from DataTable1 and DataTable2, regardless of whether the join condition is met or not.
Result as shown below:

[Jithesh_R (Jithesh Karkera)]

Hey @Tuannna1 ,
Please check this workflow

Forum.zip (1.9 KB)

Output :

Regards,

[Tuannna1 (Tuan Anh)]

@Rahul_Unnikrishnan

You can use Text to Datatable activity and convert string to Datatable. Then you can use Join datatable activity to achieve this requirement.

Hi @Rahul_Unnikrishnan
I converted to array for some reason.
I also tried when I join with type [ Full ] got the problem as shown below:

[Tuannna1 (Tuan Anh)]

Hi @Jithesh_R

I have tried it, it worked.
It creates a sequence of numbers from 0 to the last index of the NameArray array.
The for loop iterates from 0 to the last index of the NameArray array, allowing you to access each element.

Thank you very much.

[Jithesh_R (Jithesh Karkera)]

@Tuannna1 ,
Yes

Happy Automation