Data table issue

datatable
activities

#1

Hi all!
I have a data table DT1 with a bunch of columns and rows. The rows represent orders and there’s one row per order per status. So, if an order has more than one status there are several rows for the same order ID. At some point in time an order may be given a value in Attr1 and if Attr2 has certain value I would like to copy Attr1 to all rows of the same order ID. Any ideas how to solve this?

So if Attr1 is not null and Attr2=r, Attr1 should be copied to all rows with the same order ID.

This is the table now:
OrderID, Status, Attr1,Attr2
123, 1, Null, r
123, 2, Null, r
123, 3, z, r
321,1,Null, y
321,2,Null, y

This is my desired result:
OrderID, Status, Attr1
123, 1, z, r
123, 2, z, r
123, 3, z, r
321,1,Null,y
321,2,Null,y

Thanks in advance…


#2

Hi @cristian_ivanoff,

use For each Row activity

inside use if condition
Convert.ToString(row("Attr2"))="r"
true-> use assign activity
row("Attr1")="z"

update it

Regards,
Arivu


#3

Thanks @arivu96
I assume I have to sort the table first. I need to keep track of the OrderID. I don’t want to copy the value to the wrong OrderID. Also, there are OrderID’s that never get a value in Attr1 so these should not be touched…

EDIT:
First row in “for each” i check if Attr1=z and Attr2=r. But second row in loop I have to check the OrderID from the first row…


#4

Hi @cristian_ivanoff,

dt = dt.Select("",[ColumnName] asc").CopyToDataTable()


#5

@cristian_ivanoff
First try this query and get the rows
dt1=(From p in dta.Select
where not string.IsEmpty(Convert.ToString(p.Item("Attr1))) and not string.IsNull(Convert.ToString(p.Item("Attr1)))
Select p).ToArray.CopyToDataTable()

Now Run Foreach row for dt1- each row as row1
Iside that run foreach row for dta-each row as row2

use one if Condition
row1(“OrderID”).ToString.Equals(row2(“OrderID”).ToString)

Then Use Assign Activity
row2(“Attr1”)=row1(“Attr1”).ToString

dt1-Filtered DataTable
dta- Source DataTable

Regards,
Mahesh


#6

Thanks for the help!
I get this error message when trying to do this.

ErrorMessage_20180319

‘IsEmpty’ is not a member of ‘String’
‘IsNull’ is not a member of ‘String’

Any thoughts what I could do wrong?


#7

Hi @cristian_ivanoff,

Use this function
String.IsNullOrEmpty

dt1=(From p in dta.Select where not string.IsNullOrEmpty(Convert.ToString(p.Item("Attr1))) and not string.IsNullOrEmpty(Convert.ToString(p.Item("Attr1))) Select p).ToArray.CopyToDataTable()

Regards,
Arivu


#8

Thanks!
I also removed the second condition. Using this:

dt1=(From p in dta.Select where not string.IsNullOrEmpty(Convert.ToString(p.Item("Attr1))) Select p).ToArray.CopyToDataTable()

Should be the same result right?


#9

Hi @cristian_ivanoff,

Yes same result


#10

Thanks @arivu96 and @MAHESH1

Works great!