Dear friends I am stuck at one point please help me.
I have data in dt1. In that I have Account number and account holder.
First I need to check if there are any duplication account numbers.
If duplicate account numbers are found I need to check for account holder. If account holder is main i need to keep that.
If duplicate account number is found and account holder is joint I need to remove that.
If duplicate account numbers are found and both have joint I need to remove one joint from those two.
You can achieve it by using the Vb code. You can use the below Vb code in Invoke Code activity.
' Create a copy of the input DataTable to use as the result DataTable
resultTable = dt1.Clone()
' Define a list to keep track of duplicate account numbers
Dim duplicateAccounts As New Dictionary(Of String, List(Of DataRow))
' Iterate over the rows to find duplicates
For Each row As DataRow In dt1.Rows
Dim accountNumber As String = row("AccountNumber").ToString()
If duplicateAccounts.ContainsKey(accountNumber) Then
duplicateAccounts(accountNumber).Add(row)
Else
duplicateAccounts.Add(accountNumber, New List(Of DataRow)({row}))
End If
Next
' Create a list to store rows that need to be kept
Dim rowsToKeep As New List(Of DataRow)
' Process the duplicate account numbers
For Each accountNumber As String In duplicateAccounts.Keys
Dim accountRows As List(Of DataRow) = duplicateAccounts(accountNumber)
If accountRows.Count > 1 Then
' Sort the rows to prioritize "Main" account holders over "Joint"
accountRows.Sort(Function(row1, row2) row1("AccountHolder").ToString().CompareTo(row2("AccountHolder").ToString()))
' Keep the first row (either "Main" or one of the "Joint")
rowsToKeep.Add(accountRows(0))
Else
' If there is only one row, keep it
rowsToKeep.Add(accountRows(0))
End If
Next
' Add the rows to keep to the result DataTable
For Each row As DataRow In rowsToKeep
resultTable.ImportRow(row)
Next
Check the below arguments,
dt1 with In direction
resultTable with Out direction
The data without duplicates will store in the resultTable variable which is the datatable datatype.
Assign Activity arrSort | String Array = (new String() {"MAIN","JOINT"}).Reverse.toArray()
Assign Activity
dtResult | DataTable =
(From d in dtDataVar.AsEnumerable
Group d by k=d("Account Number").toString.Trim into grp=Group
Let gpo = grp.OrderByDescending(Function (x) Array.IndexOf(arrSort, x("Account Holder").toString.Trim.ToUpper))
Select r = gpo.First()).CopyToDataTable
