Need to fetch multiple columns from a data table using Linq query

Hi everyone,

I am new RPA developer. I am stuck with one use case where i need to fetch 2 columns from a datatable:
Here is the example:
I have 3 columns: SF-Case, Item And Status(Only Item column has unique id’s)

  1. I need to get distinct SF-case
  2. For each SF-case, i need to find it’s corresponding Item and Status
    So far i am able to get distinct SF-case values and Item by using below ling queries:

Using in assign activity,
dt1.AsEnumerable.Select(Function(r) r(“SF-Case”).ToString).ToArray.Distinct
Again using assign activity:
Using for each loop for sfca aaray store from above query, (Where sfca is for each loop item)
dt1.AsEnumerable.Where(Function(r) r(“SF-Case”).ToString = sfca).Select(Function(r) r(“Item”).ToString).ToArray

Issue: is i need both item and staus for each SFCA in a single linq query, how can we do that?

Thanks in advance!!

@deepika.garg
welcome to the forum

it looks like a group by case:

Maybe you can sare some sample input and expected output data. Based on this we can give the more specific solution approach

Sample Data:

Thanks Peter for the link above. What i need to do is, get the distinct SF-case and then find it’s corresponding Item and Status for each item.

SF-Case Item Status
SFCA-1 A Success
SFCA-1 B Success
SFCA-2 C Success
SFCA-3 D Success
SFCA-4 E Fail
SFCA-4 F Success
SFCA-5 G Fail
SFCA-6 H Fail
SFCA-6 I Success
SFCA-6 J Fail
Output Required:
SF-Case Item Status
SFCA-1 A,B Success,Success
SFCA-2 C Success
SFCA-3 D Success
SFCA-4 E,F Fail,Success
SFCA-5 G Fail
SFCA-6 H,I,J Fail,Success,Fail

Thanks in advance!!

Try this one:

(From d In File_Table.AsEnumerable
Group d By k=d(“SF-Case”).toString.Trim Into grp=Group
Let n = String.Join(“,”,grp.Select(Function (x) x(“Item”).toString.Trim).toArray)
Let p = String.Join(“,”,grp.Select(Function (x) x(“Status”).toString.Trim).toArray)
Let ra = New Object(){k,n,p}
Select dtreport.Rows.Add(ra)).CopyToDataTable

image

Refer link: [HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

Thanks for the LINQ query. It’s working. :slight_smile:
Apologies, slight change in output requirement:
I need List of items with Status ‘Success’ and 'Fail Separately on the basis on SF-case

Thanks in advance!

1 Like

Hi Bro.

Try this one:

(From d In File_Table.AsEnumerable
Group d By k=d(“SF-Case”).toString.Trim, k1=d(“Status”).toString.Trim Into grp=Group
Let n = String.Join(“,”,grp.Select(Function (x) x(“Item”).toString.Trim).toArray)
Let ra = New Object(){k,n,k1}
Select dtreport.Rows.Add(ra)).CopyToDataTable

1 Like

Thank you so much!! This query is working the way i want it. :slight_smile:

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