Linq query to get value of column based on the condition in another column

Help Studio
, ,
1

Hello everyone,
I have below data table. I need to get the value of column “price” for each value in “to_curr” column ,with the below condition -

  • If the value of “inverse” is ‘n’, then the “price” should come as such
  • If the value of “inverse” is ‘y’, then the “price” should be 1/price.
    Is this possible to achieve in one liner Linq query?

[date,from_curr,to_curr,inverse,price
07/03/2023 00:00:00,USD,HKD,n,7.833050000000001
07/03/2023 00:00:00,USD,SGD,n,1.34965
07/03/2023 00:00:00,USD,NOK,n,10.701
07/03/2023 00:00:00,USD,BRL,n,4.7861
07/03/2023 00:00:00,USD,PLN,n,4.0719
07/03/2023 00:00:00,USD,AUD,y,0.66835
07/03/2023 00:00:00,USD,NZD,y,0.6160500000000001
07/03/2023 00:00:00,USD,MXN,n,17.068250000000003
07/03/2023 00:00:00,USD,GBP,y,1.26895
07/03/2023 00:00:00,USD,CAD,n,1.3234000000000001
07/03/2023 00:00:00,USD,EUR,y,1.0906500000000001
07/03/2023 00:00:00,USD,SEK,n,10.852250000000002
]

2

LINQ expressions might not be the best fit for directly modifying values in a DataTable in UiPath. However, if you still want to achieve this using LINQ-like syntax, you can create a new DataTable with the modified values and then replace the original DataTable with the modified one.

You can use an assign activity with the following expression to your datatable:

(From row In dt_mydata.AsEnumerable()
             Let newValue = If(row("Inverse").ToString.ToUpper="Y",1/convert.ToDouble(row("Price")), row("Price"))
             Select dt_mydata.Clone().Rows.Add(row("Date"), row("From_Curr"), row("To_Curr"), row("Inverse"), newValue)).CopyToDataTable()

Example:

image
image1337×468 22.5 KB

Hope this is useful for you!

3

Hi @kaurM

  1. Use a Read Range activity to read the data from your Excel or CSV file and store it in a DataTable variable, let’s call it dtExchangeRates.

  2. Add an Assign activity to create a new DataTable variable, let’s call it dtResult:

   dtResult = (From row In dtExchangeRates.AsEnumerable()
               Let Price = If(row("inverse").ToString = "y", 1 / CDbl(row("price")), CDbl(row("price")))
               Select dtResult.Rows.Add(row("date"), row("from_curr"), row("to_curr"), row("inverse"), Price)).CopyToDataTable()
  1. Remember to have a DataTable variable, dtResult, created with the same columns as your original table before using the above Assign activity.

This LINQ query reads each row from the dtExchangeRates DataTable, calculates the “Price” column based on the “inverse” value, and then adds the calculated rows to the dtResult DataTable. Make sure to adjust the column names and types according to your actual DataTable structure.

Hope it helps!!

4

@kaurM

Do you just need to get the values for each iteration?

What is the downstream process?

Cheers

5

I need the values for “price” column for each iteration in a double data type variable.
For example, for first row, the “inverse” value is “n”, I need varPriceHKD = 7.833050000000001
For row 6, the “inverse” value is “y”, I need varPriceAUD = 1.49622 which is 1/0.66835
I hope this explains what I am actually looking for.

Thanks,

6

Thanks for your effort on this. Just wanted to know what other approach you would suggest to achieve this. To better explain what exactly I am looking for is -

I need the values for “price” column for each iteration in a double data type variable.
For example, for first row, the “inverse” value is “n”, I need varPriceHKD = 7.833050000000001
For row 6, the “inverse” value is “y”, I need varPriceAUD = 1.49622 which is 1/0.66835
I hope this explains well.

Thanks,