Group by - Data table

[cristian_ivanoff]

Hi all!
Any ideas how to do this “left join”?

[MAHESH1 (MAHESHKUMAR JV)]

@cristian_ivanoff
DT2= (From p In dt1.AsEnumerable()
Group By x= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}
Into Group
Select Group(0)).ToArray().CopyToDataTable()

Create a Create a List of strings

ListA=(From p In DT1.AsEnumerable()
Group By x= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}
Into GroupA= Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“Amount”).ToString)))).ToList()

Now DT2=DT2.DefaultView.ToTable(False,“OrderID”,Serial NO")

Now Add one datacolumn to DT2 and Name it as Total

Next Run one Foreach loop for DT2

Inside for each use one assign activity
row(“Total”)=ListA(dt1.Rows.IndexOf(row)).ToString

AtLast use the below Query.

DT3=(From p In DT1.Select()
Group Join q In DT2.Select
On p(“OrderID”) Equals q(“OrderID”) and p(“Serial No”) Equals q(“Serial No”) Into abc=Group
From g In abc.DefaultIfEmpty()
Select p).ToArray.CopyToDataTable()

Regards,
Mahesh

[cristian_ivanoff]

oh ok. Didnt update the site so didnt see your answer. Thanks @MAHESH1 I will try immediately…

//c

[cristian_ivanoff]

@MAHESH1 I’m sorry but I’m not used to the syntax. I get this error message. I try to search for answers but cant find any…

[arivu96 (Arivazhagan A)]

@MAHESH1

(From p In dt1.AsEnumerable()

Group By x= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}

Into Group

Select Group(0)).ToArray().CopyToDataTable()

Use this one @cristian_ivanoff
(From p In dt1.AsEnumerable() Group By x= New With { Key.a =p.Item("OrderID"),Key.b=p.Item("SerialNo")} Into Group Select Group(0)).ToArray().CopyToDataTable()

Regards,
Arivu

[cristian_ivanoff]

Thanks @arivu96
Are the quotes the difference?

[cristian_ivanoff]

I got this error message now and Im lost in space…Dont know what to do…“Hides a variable”…when im trying to create a list of strings…

[MAHESH1 (MAHESHKUMAR JV)]

@cristian_ivanoff
Try this once
DT2= (From p In dt1.AsEnumerable()
Group p By x= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}
Into Group
Select Group(0)).ToArray().CopyToDataTable()

Just add p

Regards,
Mahesh

[cristian_ivanoff]

No difference. Same error. What language is this anyway?

[MAHESH1 (MAHESHKUMAR JV)]

@cristian_ivanoff
Try this it might help you
ListA=(From p In DT1.AsEnumerable()
Group By y= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}
Into GroupA= Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“Amount”).ToString)))).ToList()

Even here also you have to use original datatable DT1, not the grouped one

Regards,
Mahesh

[balu (Bharath Yadav)]

Hi @MAHESH1 @arivu96,

how can we group by col1 and select col2 .
Example
Account Type
101 CA
102 SA
103 CA
104 CA
105 SA

Result : 101 CA
103 CA
104 CA

Thanks

[arivu96 (Arivazhagan A)]

Hi @balu,
Try this code

DataTable =DataTable.DefaultView.ToTable(true,"Account","Type")

Regards,
Arivu

[balu (Bharath Yadav)]

working but not filtering data ?
How to filter based on Type “CA” as in example??

[arivu96 (Arivazhagan A)]

@arivu96

DataTable =DataTable.DefaultView.ToTable(true,“Account”,“Type”)

DataTable =DataTable.DefaultView.ToTable(true,"Account","Type").Select("Account='CA'").CopyToDataTable()

or
DataTable =DataTable.Select("Account='CA'").CopyToDataTable()

Refer this one for DataTable operations

DataTable filtering with expressions

Hi All, Use below code to remove empty row from the table. DataTable name->DataTableName Use assign activity DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable() Re-usable Component pass Parameter as datatable you will get the answer. RemoveEmptyRows.xaml (8.5 KB) Remove rows based on particular column is empty. DataTableName.Select("Convert(column3, System.String)< >''"…

Regards,
Arivu

[balu (Bharath Yadav)]

@arivu96

DataTable.DefaultView.ToTable(true,“Account”,“Type”).Select(“Account=‘CA’”).CopyToDataTable()

Super bro
Working fine
Thanks @arivu96

[balupad14 (Balamurugan)]

Hi @cristian_ivanoff,

Try this with less code.

RPA Listings - Collections, Integration Packs | UiPath Marketplace

Regards
Balamurugan

[lainh (HIGASIKUZE TAKAYOSI)]

DataTable.DefaultView.ToTable

may be a neat method,but please notice that:
DefaultView.ToTable has a bottleneck on record numbers which is be confirmed bad performs by statistics below:

[PriyaRK (Priyanka R K)]

@arivu96, Hi,

Do you have a sample workflow that can be attached.

Thanks.

[Jyoti_Sharan1 (Jyoti Sharan)]

ListA=(From p In DT1.AsEnumerable()
Group By x= New With { Key.a =p.Item(“OrderID”),Key.b=p.Item(“SerialNo”)}
Into GroupA= Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“Amount”).ToString)))).ToList()

getting syntax error it’s saying ‘)’ expected

[huetnicolas]

Hello,
I wonder what type is declared "ListA” and how do to convert that list into a new datable ?