Creating new DT from another two. Join data table activity

[BiRobot]

Hello,

How can i use “join Data Table” activity in my situation?

I have two Datables and i want create new one from these two, matching “name” as uniqe value and multiplying amount from one dt and percentage from second dt.

DT1 ex:
Name | Amount
Joe | 10
Jane | 20

DT2 ex:

Name | Percentange project1 | Percentage project 2 | Percentage project 3
Joe | 50% | 100% |
Jane | | 30% | 30%

What i want to aquire as a new DT

NEW DT EX

NAME | Project 1 | Project 2 | Project 3 |
Joe | 5 (1050% ) | 10 (10100% ) | |
Jane | | 6 (2030%) | 6 (2030%) |

So it would join two Datables and then multiply ammount(dt1) if any percentage is provided in project columns(dt2). Apologies, hope my question is understantable

[Che]

Hi @BiRobot

The way you are going to do it all depends on the format of your starting two DataTables. How are percentages represented in DT2? If as decimals, i.e. 0.5 for 50%, then you can join the two tables, then apply computation on the two columns.

For your instance you will want to use a Left Inner Join on the column “Name”. You can use a Join DataTables activity for this.

Example:

And for your column computation see this guide:

How to Update Data Column Values of a Data Table | Community Blog

If the format of the percentage column is not decimal, you can build a new table, loop through DT1 and DT2 together, writing and computing column values that way.

Or you could modify the source of the data to use data types which are easy to work with, causing yourself less work in the long run. It’s fun in practice to write long functions, but in production its about minimalism.

Kind regards

[Shikhar_Tandon]

Hi @BiRobot,

Create a Take Build datatable activity.

@BiRobot

NAME | Project 1 | Project 2 | Project 3 |

Create a datatable with the above headers name it out_Dt then try with below query -

(From a In dt1.AsEnumerable
Join b In dt2.AsEnumerable
On a(“Name”).ToString Equals b(“Name”).ToString
Let ra = CDbl(a(“Name”).ToString)*CDbl(b(“Percentange project1”).ToString)
Let rb = CDbl(a(“Name”).ToString)*CDbl(b(“Percentange project2”).ToString)
Let rc = CDbl(a(“Name”).ToString)*CDbl(b(“Percentange project3”).ToString)
Select out_Dt.Rows.Add({a(“Name”),ra,rb,rc})
).CopyTodatatable

also let us know if you got the required soln or not.

Thanks

[BiRobot]

Thank you, i had also to update dt rows to the same value but as string and it compared correctly now in “join dt” activity. Marking as solution

[BiRobot]

Thank you, believe it`s working as well. Did not tested yet