I want a linq query where first rount of the column1 value and subtract the value from column 2 with column 1 and paste the value to column 3

Help Activities
, ,
1

i want a linq query/ vba code where first rount of the column1 value and subtract the value from column 2 with column 1 and paste the value to column 3

2

@Melbin_Antu1

YourDataTable = YourDataTable.AsEnumerable().Select(
    Function(row) 
        row.SetField("Column3", Convert.ToInt32(row("Column2")) - Convert.ToInt32(row("Column1")))
        Return row
    End Function).CopyToDataTable()

Please use invoke code

3

Hi @Melbin_Antu1

Try this linq query:

dt = (From row In dt
Let Column3 = (CInt(row("Column2"))-CInt(row("Column1"))).ToString
Select dt.Clone.Rows.Add({row("Column1"),row("Column2"),Column3})
           ).copytodatatable()

Hope it helps!!

4

first i want to round of the value in column 1. after that i want to substract the column 2 value with column 1 and paste to column 3

5

first i want to round of the value in column 1. after that i want to substract the column 2 value with column 1 and paste to column 3.

6

@Melbin_Antu1

YourDataTable = YourDataTable.AsEnumerable().Select(
    Function(row) 
        Dim roundedValue As Double = Math.Round(Convert.ToDouble(row("Column1")))
        Dim difference As Double = Convert.ToDouble(row("Column2")) - roundedValue
        row.SetField("Column3", difference)
        Return row
    End Function).CopyToDataTable()
7

Hi @Melbin_Antu1

Could you share sample input.

Regards

8

image
image1920×1080 263 KB

First i want to round off the calue in SAP Balance and after that Final AOG Value substract with SAP Balance and Paste the value to Payment Check

9

Hi @Melbin_Antu1

If possible could you share the excel.

In the mean while try this:

dt = (From row In dt
                              Let eachRow = {row("Column1"), row("Column2"), CDbl(row("Column2")) - Math.Round(CDbl(row("Column1")))}
                              Select dt.Clone().Rows.Add(eachRow)
                             ).CopyToDataTable()

Please change the column names accordingly
Regards

10

test (2).xlsx (9.6 KB)

11

image
image1920×1080 281 KB

12

you get an idea what i am saying? First i want to round off the Values in SAP Balance and after that Final AOG Value substract with SAP Balance and Paste the value to Payment Check

13

Hi @Melbin_Antu1

Try this query:

dt = (From row In dt
 Let finalAOGQuantityStr = row("Final AOG Quantity").ToString().Trim(),
     finalAOGQuantity = If(finalAOGQuantityStr = "0", "-", finalAOGQuantityStr),
     finalAOGValueStr = row("Final AOG Value").ToString().Trim(),
     finalAOGValue = If(finalAOGValueStr = "0", "-", finalAOGValueStr),
     sapBalanceStr = row("SAP Balance").ToString().Trim(),
     sapBalance = If(sapBalanceStr = "0", "-", sapBalanceStr),
     paymentCheck = If(finalAOGValueStr <> "0" AndAlso sapBalanceStr <> "0", CDbl(finalAOGValueStr) - Math.Round(CDbl(sapBalanceStr)), 0)
 Select dt.Clone().Rows.Add({finalAOGQuantity, finalAOGValue, sapBalance, paymentCheck})
).CopyToDataTable()

Hope it helps!!

14

@Melbin_Antu1
Sequence.zip (1.9 KB)
Output:

image
image512×540 13.1 KB

15

@rlgandu Before Substraction i want to round off the values in the SAP Balance after that only Substraction where do
Look the SAP Balance Value that is in decimal i want to roundoff the values first

16

LINQ query for UiPath to perform your operation:

dataTable.AsEnumerable().ToList().ForEach(Sub(row) row(“Column3”) = Math.Round(Convert.ToDouble(row(“Column1”))) - Convert.ToDouble(row(“Column2”)))

For VBA in Excel:
For i = 2 To lastRow
Cells(i, 3).Value = Round(Cells(i, 1).Value) - Cells(i, 2).Value
Next i

Note :
In both snippets, replace dataTable with your DataTable object, and in the VBA code, lastRow with the last row of your data, and ensure the column indices match your Excel sheet layout.
// * dataTable is your DataTable variable.

  • AsEnumerable() allows you to query the DataTable with LINQ.
  • ToList().ForEach() iterates over each DataRow.
  • Inside the lambda, Math.Round() rounds the value in “Column1”.
  • The result of the subtraction is then assigned to “Column3” of the same row.

Remember to replace “Column1”, “Column2”, and “Column3” with the actual column names of your DataTable.//

17

image
image1920×1080 255 KB

18

Hi @Melbin_Antu1

This query should help you

dt = (From row In dt
 Let finalAOGValueStr = row("Final AOG Value").ToString().Trim(),
     finalAOGValue = If(finalAOGValueStr = "0", "-", finalAOGValueStr),
     sapBalanceStr = row("SAP Balance").ToString().Trim(),
     sapBalance = If(sapBalanceStr = "0", "-", sapBalanceStr),
     paymentCheck = Math.Round(If(finalAOGValueStr <> "0" AndAlso sapBalanceStr <> "0", CDbl(finalAOGValueStr) - Math.Round(CDbl(sapBalanceStr)), 0))
 Select dt.Clone().Rows.Add({finalAOGValue, sapBalance, paymentCheck})
).CopyToDataTable()

Output:

image
image678×758 16.5 KB

Hope it helps!!

19

@Melbin_Antu1

YourDataTable = YourDataTable.AsEnumerable().Select(
    Function(row) 
        Dim roundedColumnA As Double = If(Not IsDBNull(row("Final AOG Value")) AndAlso Double.TryParse(row("Final AOG Value").ToString(), roundedColumnA), Math.Round(roundedColumnA), 0.0)
        Dim roundedColumnB As Double = If(Not IsDBNull(row("SAP Balance")) AndAlso Double.TryParse(row("SAP Balance").ToString(), roundedColumnB), Math.Round(roundedColumnB), 0.0)
        Dim paymentCheck As Double = roundedColumnB - roundedColumnA
        row.SetField("Payment Check", paymentCheck)
        Return row
    End Function).CopyToDataTable()

image

20

can u share sequence