Filling the data into another table based on matching value

[Brian_Henokh1 (Brian Henokh)]

hello guys, I have an example data to process

dtAnimal
Animal | Status
Dog | 001&003
Cat | 001&003

dtMaster
Animal | Vaccine
Dog | 305
Dog | 308
Cat | 306
Cow | 305
Cat | 300

rules parameter:

• Keep the matching animal
• for ‘Status’ column:
  • replaced by filling ‘Changed’ value if the ‘Vaccine’ column of dtMaster is contains ‘305’
  • if isn’t contains, will be replaced by filling ‘Checking’
• Make a final result without for each / do while activity

Expected:
Animal | ID
Dog | Changed
Cat | Checking

anyone can give me a solution?

regards,
Brian

[Parvathy (PS Parvathy)]

Hi @Brian_Henokh1

Check out the below xaml file.

Main.xaml (13.0 KB)

Use the below code in Invoke Code activity:

' Assuming dtAnimal and dtMaster are the input DataTables
Dim dtResult As New DataTable
dtResult.Columns.Add("Animal", GetType(String))
dtResult.Columns.Add("ID", GetType(String))

' List of dynamic vaccines to check
Dim vaccinesToCheck As New List(Of String) From {"305", "308", "306", "300"}

' Iterate through each row in dtAnimal
For Each rowAnimal As DataRow In dtAnimal.Rows
    Dim animal As String = rowAnimal("Animal").ToString()
    
    ' Check if the dtMaster contains a row with the matching animal and any of the dynamic vaccines
    Dim matchingRows As DataRow() = dtMaster.Select($"Animal = '{animal}' AND Vaccine IN ('{String.Join("','", vaccinesToCheck)}')")

    ' Determine the ID based on the condition
    Dim id As String
    If matchingRows.Length > 0 Then
        id = "Changed"
    Else
        id = "Checking"
    End If

    ' Add the result to dtResult
    dtResult.Rows.Add(animal, id)
Next

' Now dtResult contains the expected result

output_Dt=dtResult

Output:

Please check with your output. Is your given output correct?

Hope it helps!!

[Brian_Henokh1 (Brian Henokh)]

hello, thankyou very much for your solution… btw, can you solve this issue? I am trying using LINQ but still in error.

trying Function:
dtAnimal.AsEnumerable.Select(Function(x) dtAnimal.Clone.LoadDataRow({
x(“Animal”),
If(dtMaster.AsEnumerable().Where(Function(y) y(“Animal”).ToString.Equals(x(“Animal”).tostring) And y(“Vaccine”).ToString.Equals(“305”))
.Select(Function(y) y(“Vaccine”).tostring).FirstOrDefault.tostring,},False)).CopyToDataTable

regards,
brian