VB code to compare two data tables

Help Activities
, , ,
1

Hi All,

I am having 2 data tables and merge based condition and get all rows from dt1

ex:dt

InvoiceID Invoice name PO number total Rate Doc num
3 x 123 0 4
4 y 234 0 2
5 a 245 0 6
6 z 234 0 7

dt2

Doc Num Invoice Rate
2 y 3.5
7 z 4.21

output

InvoiceID Invoice name PO number Total Rate Doc num
3 x 123 0 4
4 y 234 3.5 2
5 a 245 0 6
6 z 234 4.21 7

Could you please provide solution in VB code(to write in invoke code)

Forum Engagement Daily Reports
2

Hi @Manaswini_UI

Try this
'Loop through all rows of dt1
For Each row1 As DataRow In dt1.Rows

'Find matching rows in dt2 based on Doc Num
Dim matchedRows() As DataRow = dt2.Select(
    "Doc Num = '" & row1("Doc num").ToString & "'"
)

'If match found, update Total Rate
If matchedRows.Length > 0 Then
    row1("Total Rate") = matchedRows(0)("Rate")
Else
    'If no match, keep Total Rate as 0 (optional safeguard)
    row1("Total Rate") = 0
End If

Next

Hope it helps

If solution help you please mark as solution

Thank You

3

You want only rows from dt1 which is matched in dt2 based on doc no column..

You can simply use join activity and use left join. Provide mapping column in condition..

If you want vb code only , then use this…
’
For Each row1 As DataRow In dt1.Rows

Dim matchRows() As DataRow = dt2.Select("`Doc Num` = '" & row1("Doc num").ToString & "'")

If matchRows.Length > 0 Then
    row1("Total Rate") = matchRows(0)("Rate")
End If

Next
’

In invoke code activity add arguments..
Dt1 - in/out
Dt2 - in

Let me know if it works :smiling_face:

4

Hi @Manaswini_UI

For Each row1 As DataRow In dt1.Rows
Dim match = dt2.AsEnumerable().
Where(Function(r) r.Field(Of Int32)(“Doc Num”) = row1.Field(Of Int32)(“Doc num”)).
FirstOrDefault()

If match IsNot Nothing Then
    row1("Total Rate") = match.Field(Of Double)("Rate")
End If

Next

5

Hi @Manaswini_UI ,

If the issue is not resolved using the above solution, you may try the approach below:

Arguments to create in Invoke Code

Name Direction Type
dt1 In/Out System.Data.DataTable
dt2 In System.Data.DataTable

VB.NET Code (Paste inside Invoke Code)

' Create a lookup dictionary from dt2 (Doc Num -> Rate)
Dim rateLookup As New Dictionary(Of String, Double)

For Each row2 As DataRow In dt2.Rows
    Dim docNum As String = row2("Doc Num").ToString.Trim
    Dim rate As Double = Convert.ToDouble(row2("Rate"))
    
    If Not rateLookup.ContainsKey(docNum) Then
        rateLookup.Add(docNum, rate)
    End If
Next

' Loop through dt1 and update Total Rate if match found
For Each row1 As DataRow In dt1.Rows
    Dim docNum1 As String = row1("Doc num").ToString.Trim
    
    If rateLookup.ContainsKey(docNum1) Then
        row1("Total Rate") = rateLookup(docNum1)
    Else
        row1("Total Rate") = 0
    End If
Next

Thanks,

6

can u send me this code xaml

7

@Manaswini_UI please use the code which I shared with you and let me know if u still face issue.

8

you could have simply done it if you have this data in an excel.
this is basically a Vlookup on your 2 data sets updating your “total rate” in dt1..
in case you want to it in a simple way
dont read these 2 dataset into datatable yet. (i’m assuming these data sets are in 2 different sheets in a single excel file)

  1. add Vlookup formmula : =IFERROR(VLOOKUP(@B:B,Sheet2!B:C,2,0),0)
  2. apply to all the available rows.
  3. then read the result into you datatable using Read range.

the result would look the same.

image
image507×193 4.28 KB