Select query help

@THIRU_NANI

As here i am not updating for particular column, this wont help

Name. Value. Amount pending
Shilpa. 56886. 747
Liper. -241. -241
Hudd. -241. 786

Data will be like this, where multiple volumn can have value as -241
I want to change only value column

@kirankumar.mahanthi1

for your table try below query it will give you correct resut.

(From d In dtData.AsEnumerable
Let u = If(d(1).ToString.Trim.Equals(“-241”),“0”,d(1).ToString)
Select dtResult.Rows.Add(New Object(){d(0),u,d(2)})).CopyToDataTable

Instead of d(1) can i gv d("value)

@kirankumar.mahanthi1

yeah try like that will work i guess. relace d(1) with d(“value”)

Refer the attached updated work flow check and let me know is that what you are expecting.

Linq Demo.zip (10.0 KB)

@kirankumar.mahanthi1

Name. Val. Amt. AmtPending. Amttaken
Shul. 56. 765. -241. -241
Gut. 787. -241. -241. 567

Any way to update only AmtPending and amttaken column for val -241 to 0

Name. || Val. || Amt. || AmtPending. || Amttaken
Shul. 56. || 765. ||-241. || -241
Gut. 787. || -241. || -241. || 567
Any way to update only AmtPending and amttaken column for val-241 to 0

I dont have to chsnge Amt column

for AmtPending

(From d In dtData.AsEnumerable
Let u = If(d(3).ToString.Trim.Equals(“-241”),“0”,d(3).ToString)
Select dtResult.Rows.Add(New Object(){d(0),u,d(2)})).CopyToDataTable

for Amttaken

(From d In dtData.AsEnumerable
Let u = If(d(4).ToString.Trim.Equals(“-241”),“0”,d(4).ToString)
Select dtResult.Rows.Add(New Object(){d(0),u,d(2)})).CopyToDataTable

Regards,
NaNi

try like below.

(From d In dtData.AsEnumerable
Let u1 = If(d(“AmtPending”).ToString.Trim.Equals(“-241”),“0”,d(“AmtPending”).ToString)
Let u2 = If(d(“Amttaken”).ToString.Trim.Equals(“-241”),“0”,d(“Amttaken”).ToString)
Select dtResult.Rows.Add(New Object(){d(“Name”),d(“Val”),d(“Amt”),u1,u2})).CopyToDataTable

refer the attached updated work flow for your reference. thanks.

Linq Demo Updated.zip (9.7 KB)

@kirankumar.mahanthi1
I have 83 columns, any way to copy the columns directly

@Shilpa_Mohanty could you please tell us the complete requirement, so we can have a proper view on it.

Thanks

@prasath_S
I have 85 columns.
5 columns have values “-241”
I want to update “-241” to “0” in 3 columns out of 5
Thats the complete requirement

@prasath_S and @kirankumar.mahanthi1
can you please help me

@Shilpa_Mohanty Please try this,

1. Use invoke code activity,

image

2. In edit arguments pass datatable as below,

Code inside invoke code,

For Each dtRow As DataRow In dt.Rows
	For Each colName As String In {"value","AmtPending","Amttaken"}
		If (dtRow(colName).ToString = "-2.41") Then dtRow(colName) = 0
	Next colName
Next dtRow

Thanks

1 Like

@prasath_S
May I know what is dt1

As I took readrange and stored the value in dt

@prasath_S
Facing below error,
image

for general introduction

for a first analysis / selection approach we would recommend also, to check how many rows are affected. (Just use a count statement)

Then lets decide, which approach will fit the best

Yeah you should replace dt1 with your data table contains those 83 columns.

@ppr

Tried invoke code and getting the error screenshot which is pasted in the thread
Tried linq but here manually I have to pass all the column names
Cant use for each as I have 40k records of data

NOTE: there are around 1000 rows in each column, which has -241 value, I have to change it to 0


How to overcome this