Need LINQ expression for multiple Group by

[Marian_B (Marian)]

Hello Guys,

I need the LINQ expression for the following input Source.

I have attached input and output

Input.xlsx (10.1 KB)

Need to Group Multiple columns → REGNO
, Column 6 , Here in the Column 7 column I need to apply the condition and group the values :

1. If the column contains EAZ Or EANW Need to return EA (Need to group REGNO and Column 6 and take the value in the Column 7)

2. If the column contains EAZ Or EANO Need to return EA (Need to group REGNO and Column 6 and take the value in the Column 7)

3. If we have any other value need to keep that value in the data table

Need to sum there columns Column 8 Column 9 Column 10 (Based on Group by)

Note : In my original input i have 48 Columns

@supermanPunch @ppr @Yoichi

[Anil_G (Anil Gorthi)]

Hi @Marian_B

Please modify this as per your requirement

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let ra = New Object(){a, b, c, d}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

this is grops by ID,Code and country column you can change the column names as needed

and taken the sum of Value column can be replaced as well

final dt will contains ID,Country,Code and sum if you need more columns replicate like ‘Let b =…’ as many times as you need and include them in ‘Let ra = New Object(){a, b, c, d}’

cheers

[Marian_B (Marian)]

Thanks for your Respond @Anil_G I need to check the Column 7 based on that i need the expression

Input

Output

[Anil_G (Anil Gorthi)]

Hi @Marian_B

Please replace the Column& name in the provided expression and you should have it

cheers

[Marian_B (Marian)]

@Marian_B

• If the column contains EAZ Or EANW Need to return EA (Need to group REGNO and Column 6 and take the value in the Column 7)
• If the column contains EAZ Or EANO Need to return EA (Need to group REGNO and Column 6 and take the value in the Column 7)

In the above expression where did you process these condition? @Anil_G

Can you please guide me on that

[Anil_G (Anil Gorthi)]

HI @Marian_B

Sorry missed that condition

Please find below with modifications for column 7 Check

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let e = If({"EAZ","EANW"}.Any(function(x) String.Join(",",grp.Select(fucntion(x) x("Column7").ToString)).Contains(x),"EA","NA")
Let ra = New Object(){a, b, c, d, e}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

cheers

[Marian_B (Marian)]

@Marian_B

If the column contains EAZ Or EANO Need to return EA (Need to group REGNO and Column 6 and take the value in the Column 7)

You have missed another condition also @Anil_G

[Anil_G (Anil Gorthi)]

Hi @Marian_B

Aren’t both returning EA?

cheers

[Marian_B (Marian)]

Yes @Anil_G

After the Group By expression If the column value contains EAZ,EANW and EAZ Or EANO Need to Return EA

Note Based on the Group by i need to use If activity

@Marian_B

Input

Output

[Anil_G (Anil Gorthi)]

@Anil_G

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let e = If({"EAZ","EANW"}.Any(function(x) String.Join(",",grp.Select(fucntion(x) x("Column7").ToString)).Contains(x),"EA","NA")
Let ra = New Object(){a, b, c, d, e}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

Hi @Marian_B

Please see below

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let e = If({"EAZ","EANW","EANO"}.Any(function(x) String.Join(",",grp.Select(fucntion(x) x("Column7").ToString)).Contains(x),"EA",grp.Select(fucntion(x) x("Column7").ToString).FirstOrDefault)
Let ra = New Object(){a, b, c, d, e}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

What this condition does is if after grouping in the column 7 if it has any of these “EAZ”,“EANW”,“EANO” then that row get ‘EA’ else that row gets first value which is present in that group of values from column7

cheers

[Marian_B (Marian)]

Based on the Column 6 I need use Group by and take the value in the Column 7 @Anil_G

[Anil_G (Anil Gorthi)]

@Anil_G

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let e = If({"EAZ","EANW","EANO"}.Any(function(x) String.Join(",",grp.Select(fucntion(x) x("Column7").ToString)).Contains(x),"EA",grp.Select(fucntion(x) x("Column7").ToString).FirstOrDefault)
Let ra = New Object(){a, b, c, d, e}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

Hi @Marian_B

To be precise on column 6 itself please see below

(From x In dt.AsEnumerable
Group x By a = x(“Column6”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Column8”).ToString)).ToString
Let e = If({"EAZ","EANW","EANO"}.Any(function(x) String.Join(",",grp.Select(fucntion(x) x("Column7").ToString)).Contains(x),"EA",grp.Select(fucntion(x) x("Column7").ToString).FirstOrDefault)
Let ra = New Object(){a, b, e}
Select dt.Rows.Add(ra))).CopyToDataTable

This will have 3 columns 1 (the Groupdby column6) 2 (Column7 containign Conditions) and 3 (Sum containing the cum of Column8)

When you copy paste please replace the inverted comma

If you want to include the value of any other columns then add
‘Let f = grp.Select(fucntion(x) x(“Column2”).ToString).FirstOrDefault’
and also add ‘f’ in the line like this ‘Let ra = New Object(){a,b,e,f}’

This can be done for any column you need by changing the column name and corresponding assigned value

cheers

[Marian_B (Marian)]

It is not working @Anil_G Can you please check one again the input and expected output

[Anil_G (Anil Gorthi)]

Hi @Marian_B

May I know what is missing?

cheers

[Marian_B (Marian)]

I need to Group all the 3 columns. In the Column 7 I have Condition that was mention in the above post @Anil_G

(From x In dt.AsEnumerable
Group x By a = x(“REGNO”).ToString.trim, c= x(“Column 6”).ToString.trim, d= x(“Column
7”).ToString.trim Into grp = Group

[Anil_G (Anil Gorthi)]

@Anil_G

If({"EAZ","EANW","EANO"}.Any

Hi @Marian_B

This is exactly with the columns as well… For the EA piece you have to replace the column values before this step. You can either use a for loop to do that or use a invoke code

This to be in invoke code

dt.AsEnumerable.ToList.ForEach(Sub(row)
    If {"EAZ","EANW","EANO"}.Any(Function(x) row("Column 7").ToString.Contains(x)) Then
row("Column 7")="EA"
    End If
End Sub
)

This one in Assign

(From x In dt.AsEnumerable
Group x By a = x("Column 6").ToString.trim,b = x("REGNO").ToString.trim,l = x("Column 7").ToString.trim Into grp = Group
Let c = grp.Sum(Function(p) CDBL(p("Column 8").ToString)).ToString
Let d = grp.Sum(Function(p) CDBL(p("Column 9").ToString)).ToString
Let e = grp.Sum(Function(p) CDBL(p("Column 10").ToString)).ToString
Let f = grp.Select(Function(p) p("Column 11").ToString).FirstOrDefault
Let i = grp.Select(Function(p) p("Column 3").ToString).FirstOrDefault
Let j = grp.Select(Function(p) p("Column 4").ToString).FirstOrDefault
Let k = grp.Select(Function(p) p("Column 5").ToString).FirstOrDefault
Let h = grp.Select(Function(p) p("Date").ToString).FirstOrDefault
Let ra = New Object(){h, i , j , k, l , a, b, c, d, e, f}
Select dt.Rows.Add(ra)).CopyToDataTable

Output from bot

This is how the workflow looks

Hope this resolves your issue

cheers

[Anil_G (Anil Gorthi)]

Hi @Marian_B

If its resolved please mark the solution so that it helps others with similar query. If not happy to help.

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has fo…

cheers

[Marian_B (Marian)]

@Anil_G

dt.AsEnumerable.ToList.ForEach(Sub(row)
    If {"EAZ","EANW","EANO"}.Any(Function(x) row("Column 7").ToString.Contains(x)) Then
row("Column 7")="EA"
    End If
End Sub
)

for the above expression I’m getting Blank output @Anil_G

[Anil_G (Anil Gorthi)]

Hi @Marian_B

Can I see how the invoke code looks ?

and did you pass the dt as input argument?

cheers

[Marian_B (Marian)]

Yes @Anil_G i have passed the argument