Look up and merge/create data tables to create a desired outcome

[wth1993]

Hi there. I am stuck at a task where I need to manipulate data table functions. However, I don’t know how the it could be done.

Currently, I have 2 data tables - which I call DT1 and DT2. Values in them are all strings.
DT1 is a list of numbers. DT2 consists of 3 columns, i.e. Number, Date, and Transaction Amount.
What I want to do is that for each row in DT1, find if there is the same number in DT2, and if there is, return the corresponding date and transaction amount to DT1, or create DT3 to store it. If the number could not be found in DT2, return “Not Found” in both columns (Date & Transaction Amount).

Please find the illustration below:

[NIVED_NAMBIAR (NIVED N)]

Hi @wth1993

Try this way.

1. Read the datatable DT1 AND DT2 using read range.

2. Build a datatable DT3 which stores the output results using build datatable activitiy.

3. Use for each row to loop through each row of DT1

Inside the for each row, do the following .

Using the lookup datatable activitiy to lookup up.the number in DT1 in DT2 and extract the corresponding data, for lookup datatable activitiy configure following properties.

Lookup datatable : DT2

lookup value: row(“Number”).ToString

Lookup Column Name : Number

rowindex : index, where index is int32 varaible which stores the row index of lookup value in DT2

then use if Condition with condition as

index <>-1

Then

Use add datarow activity with adding
{row(“Number”).ToString,DT2.Rows(index)(1).ToString, DT2.Rows(index)(2).ToString} to DT3

Else:

{row(“Number”).ToString,“Not Found”,“Not Found”} To DT3

Outside the loop use write range activity to write the DT3

Hope it helps you

Regards

Nived N

Happy Automation

[moenk (Thomas Meier)]

What you want to do is a left join, not an inner join. See join type for details:

Join Data Tables

[wth1993]

Hello Nived, I am lost at the add datarow activity. Could you advise where to input {row(“Number”).ToString,DT2.Rows(index)(1).ToString, DT2.Rows(index)(2).ToString} to DT3? The Add Data Row activity only has 3 Input properties, i.e. ArrayRow, DataRow, and DataTable.

Thanks…

[NIVED_NAMBIAR (NIVED N)]

In array row @wth1993

[wth1993]

Thanks, Nived! That’s exactly what I wanted!

For the record, I tried the Join Data Table activity but it seemed like it doesn’t output the result I wanted. Empty value is acceptable if the number is not found, but the output data row has to be in the same order as the DT1. Moreover, there seems duplicated columns occurred.