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%
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
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:
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.
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.