Merge datatables problem

Help
#1

Hello,

I had two datatables
Dt1
Column1. Column2
SU. 12
SU. 4
BS. 13
BS. 14
FS. 05
FS. 06
Dt2
Column1. Column2
BS. 06
BS. 07
FS. 08
FS. 09
SU. 01
SU. 02

I need to merge thèse in à datatables as following:
ID. Colum1 column 2 différence
SU. 12. 01. (12-1)
SU. 4. 02. (4-2)
BS. 13. 06. (13-06)
BS. 14. 07. (13-07)
FS. 05. 08. (08-05)
FS. 06. 09. (09-06)

Any Idea?

#2

Hi.

From the looks of it, you will need to use a loop and Assign activites, rather than the Merge activity.
You can probably use a .GroupBy() line of code too but I can’t say I know it very well.

so to do this with a loop, you basically just need to “Add column” and Assign to rename columns.Then, run through dt1 in a For Each with another For Each for dt2 inside where you can pull those items that match.

Assign dt1.Columns(0).ColumnName = "ID"
Assign dt1.Columns(1).ColumnName = "Value1"
Add Data Column // to dt1 with "Value2" for columnname
Add Data Column // to dt1 with "Diff" for columnname

Add Data Column // to dt2 in order to cross off when a match is made

For each row1 in dt1
    For each row2 in dt2
        If row1(0).ToString.ToUpper.Trim = row2(0).ToString.ToUpper.Trim AND row2(2)=""
            Assign row1("Value2") = row2(1).ToString.Trim
            Assign row1("Diff") = CInt(row1("Value1").ToString.Trim) - CInt(row1("Value2").ToString.Trim)
            
            Assign row2(2) = "X" // used to cross off row when it has been used
            Break

So that’s pretty much it.
—You set up data table with the 4 columns
—Add 1 to keep a status on second table to cross off when a row is used
—Then, Loop through each row of first table and match with second table
—If match is found and has not been added yet, then Assign value to first table and take difference

There are ways to use vb.net to make this more efficient as well, and there are better solutions I’m sure. But, I hope this helps with my limited time to provide an answer.

Regards.

#3

Hi Clayton;

i had used your methodology but the “vakue2” and “diff” are empty.

#4

Hello,

it does not function, the cross fonctionality did not work.

test2.xaml (17.0 KB)

#5

Hi.

Looks like everything was correct except you needed to break out of the interior For Each so it won’t look at the rest of the table on the item from dt1 after it finds the match.

Placing the Break activity made it function correctly, like this:
image

Hope that helps. Regards.