Merge Data tables to one

Hey - wondering if anyone can help on the below.

I have two data tables (as seen in the screen grab below). I want to compare the column ‘MaterialCode’ from DT1 against the column ‘Model’ in DT1 and if there is a match take the ‘VendorNumber’ from DT2 one and add it to that row in DT1. Would anyone know the best way to do this?

@Cormac
You can use a Join Datatable activity:
left table: dt1
right table dt2
Join Type: left join
Join Cols: left:MaterialCode, Right: Model

on the joined datatable it can evaluated: if Model is not empty then it was a matched
however the unneeded column can be removed from Join Result Datatable with:

  • filter datatable, column settings
  • datatable Select method:
    • define a string array with the cols to keep from Join Result Datatable arrColNames
    • assign activity left side: dtResult, right side: dtJoinResult.DefaultView.toTable(False, arrColNames)

the result should be very close to your expected output

Hi @ppr I am trying to use the following expression in the for each but keep getting the error ‘Assign: Index was outside the bounds of the array.’ Would you have any idea why this is happening?

May we ask you on what was done implemented so far (best if it can refer to the suggested steps from above).

It looks like you try to set up a join manually with a for each row

Hi @ppr - I have attached the project file to this. The .select query is what I am struggling with

TestProcessDTSelect.zip (28.2 KB)

yes it looks like you implement a join / similar filter on a for each row base. e would suggest to use the join datatable activity instead.

have a look here and refer to the suggestions from above: