Merge Data tables to one

Help Activities
, ,
1

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?

image
image1820×936 107 KB

2

@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

3

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?

image
image830×450 12.2 KB

image
image1161×674 27.1 KB

4

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

5

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

TestProcessDTSelect.zip (28.2 KB)

6

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: