Select query help

Help Studio
,
1

Can any one help me in writing a query to update value as “0” in column “value” where the value is “-2.41”
Need the query without using for each loop as I have 40k rows.

2

Have you tried with update row item activity?

have a view on this thread

please refer

Regards,
NaNi

3

@THIRU_NANI , tried but not working

4

Have you tried with LinQ?

Regards,
NaNi

5

@THIRU_NANI
Tried the below query but I have to put the formula in column “Value” . How to achieve that
(From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ",“NA”)).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

6

Hi!

I don’t have much knowledge with LinQ.

@ppr
@kirankumar.mahanthi1
@Palaniyappan
@Yoichi
@prasath_S
@prasath17 these ppl will help you in that.

Regards,
NaNi

1 Like
7

@ppr and @kirankumar.mahanthi1
Can you please help

8

Hi ,

I tried a method using lookup value . But it will be useful only when you want to update value .

Update.xaml (9.1 KB)

9

@Priyanka_Ramesh
I guess linq would be faster and it doesnt require 3 more additional activities, 1 assign will solve the problem

10

Hi,

Use below linq query and let me know are you able to get the correct output.

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

above change the column numbers accordingly. please refer the attached code for reference. thanks.

Update_Column_Linq.xaml (7.3 KB)

1 Like
11

Hi! @kirankumar.mahanthi1

Input:

Inputdt

Output:

Output

It’s replacing the values which is -2.41. what about remaining values? those are disappeared

Regards,
NaNi

12

Hi @Shilpa_Mohanty

Please try below steps,

Read the excel sheet data or use build datatable and store the data in the datatable variable name dt.

Drag and drop the invoke code activity.

In edit arguments, create a variable dt, set direction as in/out and pass value as dt.

In edit code type the following,

dt.asenumerable.where(function(row) row("value").tostring.trim.equals("-2.41")).tolist.foreach(sub(row) row(“value”)= 0)

Write the datatable back into the excel using write range activity.

1 Like
13

use the latest expression i have mentioned above

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

i have missed in the first query above one i have tried it was working fine. could you pls try and let me know Nani.

14

@kirankumar.mahanthi1

I have to update for column “value” only
How can I specify that

15

@prasath_S
Will try and update in evening
Thanks

1 Like
16

in the below query

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

example in the above query d(1) referring the second column d(0) referring the first column.

you have to adjust based on your requirement.

1 Like
17

@kirankumar.mahanthi1 can you please update this linq query, it will be very helpful for me

18

working fine…!!

@Shilpa_Mohanty You can Mark @kirankumar.mahanthi1 post as solution. this might helpful for others

Regards,
NaNi

1 Like
19

Could you please share your input excel so that we will update query and share it with you. thanks. or else you can use the @prasath_S solution that looks more robust and you no need to worry about column indexing. thanks

20

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