If Column Z = x update Column Y

Help Studio
#1

Hi All,

I’m trying to write some code to update a very large datatable (100,000 rows) where I have a Transaction Type in a column. Based on the code written in the Transaction Type column I need to update another column. Using a For each works well but it takes 10 minutes to update all the rows, could anyone help me with LINQ or some code I could use to update these rows a lot faster please?

Thank you for your help. I have uploaded a simplified version of what I’m working with.

sample.xlsx (8.5 KB)

1 Like
#2

Hi,
Am I understand you correctly, you have a separate dictionary (or dt) of rules for changes and you wanna implement smth like
for each row
if row(‘transactiontype’) is in rules.keys
then row(‘targetcolumn’) = rules.getvaluebykey( row(‘transactiontype’) )

#3

Hi So in the example where the Transaction Type if IN the Type would then be specified as an invoice, I call the invoice text from an string type argument.

#4

If Column Z = x update Column Y

What do you want to put into column Y?

#5

So if Column “Transaction Type” = “In” update Column “Type” to Invoice.

#6

This topic deals with your problem

#7

Thanks but this is replacing a value, I basically need the logic

If Transaction Type = “IN” update Column Type to “Invoice”

I’m not looking to modify the Transaction type column.

Any ideas how I can achieve this? Thanks

#8

Ok, that was a tricky one

dt2 = (From a In dt.AsEnumerable()
Select dt2.LoadDataRow (
New Object() { a.Field(Of String)("TransType"), If(a.Field(Of String)("TransType")="IN","Invoice",a.Field(Of String)("Type"))},False)).CopyToDataTable

upd. I will explain. In my case dt and dt2 have only 2 columns: TransType and Type
If your dt has more columns, e.g. Col1, Col2, Col3, TransType, Col5, Type, Col7,
then your linq will be
dt2 = (From a In dt.AsEnumerable()
Select dt2.LoadDataRow (
New Object() { a.Field(Of String)("Col1"),a.Field(Of String)("Col2"),a.Field(Of String)("Col3"),a.Field(Of String)("TransType"), a.Field(Of String)("Col5"), If(a.Field(Of String)("TransType")="IN","Invoice",a.Field(Of String)("Type")),a.Field(Of String)("Col7")},False)).CopyToDataTable

#9

Thanks! it worked perfectly!

#10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.