Is it possible to compare values from different rows within the same data table?

Help Studio
,
1

Hello,

I have one datatable with General Ledger data (debits and credits). I’m trying to figure out a way to match the debit and credit transactions based on the location of the entry.

For transactions with a matching location I would like to take the difference between the value in column2 and column3 (which are on separate rows within the same table) based on there shared location.

There’s a sample below of how the data might look. In this example I would want to know that the difference for location 1 is $0, and location 2 is $7.00.

Date Debits Credits Location
08/31/20 100.00 0.00 1
08/31/20 0.00 100.00 1
08/31/20 287.00 0.00 2
08/31/20 0.00 280.00 2

Does anyone know if this is possible?

3

you can specify the row index you’d like to compare. So you can compare row(0).item(“Debits”) and row(1).item(“Credits”) to each other.

In practicality, I would use a for each row loop and flip a flag so it skips every other row. If Flag Then Assign Flag = flipped flag, Continue Else (leave this side blank). You also want to skip the first row, as a previous row doesn’t exist) if rowIndex = 0 Then (leave this side blank) Else (row.item("Debits") - row.item("Credits")) - (dt1.rows(rowindex-1).item("Debits") - dt1.rows(rowindex-1).item("Credits")) Flip the flag at the end.

If it’s possible the locations are not sequential, then you should use LINQ instead, but that is much more complicated to understand and maintain

EDIT: If it’s possible there are more/less locations than 2, my solution would also not work and I’d recommend going the LINQ route for that as well (you’d use a GROUP BY function, then sum credits as negative and debits as positive, then add them together to get the sum as a new column in the table returned from your LINQ)

4

Hi @amanda.smith

try this way hope it may works

  1. read the datatable and store it in a variable dt1

  2. use a while loop with condition, dt1.Rows.Count >0

  3. inside the while loop , use for each row condition, inside the for each row use the steps below:

a) use filter datatable activitiy with filter datatable as dt1 with filter condition as

“Location” = cint(row(“Location”).ToString)

In output , save the filter data in dt3 datatable

b) use the following one to find the difference between the values ,
cint(dt3.Rows(0)(1)) - cint(dt3.Rows(1)(2)) and store the value in a variable let’s say diff

c) then again use the filter datatable activitiy with input datatable as dt1 and output as dt1 with same filter condition above but instead of keep use delete so as to delete the filtered data from the datatable ( please note, it will not delete the data from.excel file)

d) use break condition to break the for each loop

  1. next same procedure will repeat , it will.go to while loop and check whether dt1 has datarow after filtering finally , and then execute the remaining steps as discussed above

Hope it helps

Mark it as solution if you got it

Nived N :robot:

Happy Automation :relaxed::relaxed:

5

@amanda.smith sorry, reading my response it was quite confusing so I attached a workflow which shows how it works much easier: amanda.smith.xaml (11.0 KB)

1 Like