Compare current and previous vlaue from data table column/row

Help
1

Hi guys,

I need help for following problem.

I have a data table, and a robot which is processing different values from specifed coulmn.
I need activitiy which will compare current value from that column with the previous one, and based on the result(false/true) robot will make decision(If activitiy).
I tried to do somethig with IndexRow formula but without success.

Any hints how to solve this?

Thanks.

2

theTable(theTable.rows.indexof(row))(colIndex) = theTable(theTable.rows.indexof(row)-1)(colIndex)

3

Hi Tiberiu,

Am I doing something wrong?
Data table name is: DT1
Coumn name: Creditor

DT1(DT1.rows.indexof(“Creditor”))(colIndex) = DT1(DT1.rows.indexof(“Creditor”)-1)(colIndex)

Thanks.

4

yep, replace “creditor” with row, assuming you’re in a for each row activity.

5

something like DT1(DT1.rows.indexof(row))(colIndex) = DT1(DT1.rows.indexof(row)-1)(colIndex)

6

My data table has data in few columns, and I want to compare data only from one.
I guess I have to mention somewhere which column I want to compare data from?

7

so, let’s assume the column you want to get values from is column A in excel or 0 in the datatable. the following formula, DT(0)(0) will return you the value from the first row and first column. so, if you’re in a for each row activity, dt.rows.indexof(row) will return the index of the current row.

now, using the same logic DT(DT.rows.indexof(row))(0) will return the value on column ‘A’ of the current row and DT(DT.rows.indexof(row)-1)(0) will return the value on column ‘A’ of the previos row. if you want to get the values from another column, replace the ‘0’ with the index you need. keep in mind that in datatables, the indexing starts from 0, so 0 will be ‘A’ 1 will be ‘B’ and so on. hope i’m clear.

8

Yes you are clear :slight_smile:

So I guess this should be the formula:

DT1(DT1.rows.indexof(row))(4) = DT1(DT1.rows.indexof(row)-1)(4)

However I get Complier error message…am I missing out something?

Thanks for your kind help.

9

DT1(DT1.rows.indexof(row))(4).toString = DT1(DT1.rows.indexof(row)-1)(4).toString try this thing

10

It is fine now, many thanks! :slight_smile:

I have one question off topic. If we presume we have the same data table with some values which are processed by the robot and for some of them we get exeption. Is it possible that for that row where exeption occured to insert a comment in first free cell of that row, for example in first blank column of input excel file? So when robot finish all rows, I can see in excel for which of them I had exception case.

11

dt.columns.count will return how many columns are in your datatable.
chr(dt1.Columns.count+65) will return the letter corresponding to the number of columns you have. assign chr(dt1.Columns.count+65).toString to a variable, let’s call it letter, then use a write cell activity with letter+(dt.rows.indexof(row)+1).toString. That 65 is used to convert ASCII to a character. (http://www.asciitable.com/)

image
image726×394 12.6 KB

12

Thanks Tiberiu, I will try this out next week and let you know how it works. :slight_smile:

Thank you one more time for your kind help!

1 Like
13

Hi Tiberiu,

I tried to run the robot but I get below error message.
Do you maybe know what might be causing the problem?
You can also see the form of excel input template.
Thanks.

image
image922×500 121 KB

14

if the error appears on the first iteration, the problem might be that it tries looking for dt(-1) which does not exist. put the if in a try catch or add another condition dt1.rows.indexof(row)>0

15

Hi Tiberiu,

Finally got time to set this up, it works great now! :slight_smile: Thanks a lot!

Can you also help me with this problem:
So I have a data table with data from columns A to column F. Now, for example first 15 cells of column F are populated and from the 16. to the bottom all are empty. I have some activities which have to use data from the same row as the first empty cell from column F. Those data are in columns D and E. How can I get those data?

Thanks.