How to add values together from different datables

Is there a way to merge data tables together (same structure) in terms of values. For example:!

Hi

Welcome back to UiPath forum

Yeah we can
Get them as three separate tables and merge them with MERGE DATATABLE activity

The steps involved is

— use a EXCEL APPLICATION scope and pass the file path as input and inside the scope use a read range activity with specific rangeand get the output as datatable variable named dt1

—again use another read range with the next cell range and get the output as dt2

—one more read range and output as dt3

—now use a MERGE DATATABLE activity and pass dt1 as source and dt2 as destination

Again use one more merge datatable activity and mention dt2 as source and dt3 as destination

Hope this would help you resolve this

Cheers @CC_Pet

Hi @Palaniyappan, thanks for your reply but I think I was not clear enough and have updated the example image. When I merge the data tables, I need it to add the values based on the first column identifier. So bot needs to get the value with the id “a” in DT_1 and add it with the one in DT_2. This has to be done for each column.

My original plan was to do a For Each Row loop of DT_1 and then a For Each loop of an array initialized with column index inside it. For the For Each loop I will do a Lookup activity to DT_2 based on col1 and then get the correct values then combine it with DT_1. Lastly, I need to add these values into DT_3 which already has a fixed structure. There is also a scenario where I need to do the merging with 3 data tables.

Is there a more elegant way to do this? Or is this the best solution.

We do understand the requirement as following
dt1:Col1=A:Col2=1 + dt2:Col1=A:Col2=3 == dtResult:Col1=A:Col2=4
And so on for the others rows and cols

You can do it as following

Merge all datatables into one datatable (e.g. Merge datatable) - dtCombined
Then calculate the sums grouped by Col1 into a new datatable - dtResult:

(From d In dtCombined.AsEnumerable
Group d By k=d("Col1").toString.Trim Into grp=Group
Let rs = {1,2,3,4}.Select(Function (x) grp.Sum(Function (g) CInt("0" & g(x).toString.Trim))).Cast(Of Object).toArray
Let ra = rs.Prepend(k).toArray
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

For more details on GroupBy have a look here:

here you will find also options on how to group data and processing the group members with a No LINQ or LINQ-For each approach

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.