Join different tables in third table who has predefined columns

Help
, ,
1

Hello UiPath Jedi’s

I need little help with this small automation, I tried to add values from dt1 and dt2 in predefined table dt3

First dt1
image

Second dt2

image

and Third dt3 will look like this

image

And in Total Amount I must sum Amount_1 and Amount_2

How can I do that

Thank you all

2

you can do 2 For each and use add data row activity to insert all rows in dt3, for the total you can do the sum manually…

3

I tried that, when I write values in Amount_2 I have same value for Id’s. Is possible to show me how?

4

you will need to add all values from dt1 to dt3, but when in for each of dt2, you need to check if ID is already in dt3 before add… if you can share what you already have i can help the rest.

5

Hi
Hope these steps would help you resolve this
—let’s take like we have three different datatable named dt1, dt2, dt3
—and assuming that in dt3 we have Calum only in column ID
—now use a FOR EACH ROW loop and pass the variable dt3 as input and inside the loop use a Lookup datatable activity and mention as these in property panel
Input value = row(“ID”).ToString
Datatable = dt1
Lookup column name = “ID”
Target column name = “Amount”
Output result = str_output1

And add another lookup datatable activity and mention as these in property panel
Input value = row(“ID”).ToString
Datatable = dt2
Lookup column name = “ID”
Target column name = “Amount”
Output result = str_output2

Where str_output1, str_output2 is a variable of type string defined in the variable panel

And next to these two lookup datatable activity use a assign activity like this
row(“Amount1”) = str_output1.ToString
And
Another assign activity like this
row(“Amount2”) = str_output2.ToString

And a final assign activity
row(“Total Amount”) = Convert.ToInt32(str_output1.ToString)+Convert.ToString(str_output2.ToString)

Cheers @Predator

1 Like
6

@Predator

With following settings the implementation can be reduced to a single for each:

If an ID is not existing in dt1 or dt2 - 0 will be returned
the lookup for an ID will be done in one iteration into dt1 and dt2

so we can implement like:

grafik
grafik371×569 11.8 KB

Sure we have to prepare some datatables. And the lookups internarly have also to do some works.

Find DEMO XAML here: Predator.xaml (16.4 KB)

Let us know your feedback and open questions. Flag the solving post as solution, so others can benefit from it. Thanks

7

I have just finished building a Robot with a similar problem.

Assuming DT1 and DT2 are as you specified, I would use the Programming.DataTable.JoinDataTables activity to join DT1 and DT2 using a Join Type = FULL (aka Outer Join in SQL), Join on DT1.id = DT2.id and output DT3.

DT3 will have columns id, amount and amount_1 (assuming the amount column is named the same in both DT1 and DT2, the join activity automatically makes the second identical column name unique by adding _1).

Then use Programming.DataTable.AddColumn to add the “Total Amount” column to DT3.

If you want to rename the Amount and Amount_1 columns, you can do so by Invoking a little VB.Net code"

DT3.Columns(“Amount_1”).ColumnName = “My New Name” . (Or something close to that, I have not tested it).

And then finally a simple for each row activity setting Total = Amount + Amount_1. You will have to handle the possibility that amount or amount_1 can be nothing as they will not default to 0 when the join does not match id’s in both tables. Do an if test on amount_1 is nothing then amount_1 = 0 before the addition assignment.

– M

8

Thank you all, I appreciate everything what you done for me.

9

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