Group by - Data table

Help
1

Hi all,
I have been searching the forum but cant find any help.
I have a data table DT1 that contains approx 20 columns. I would like to do a group by, insert the result in a new data table DT2 and do a left join between DT1 and DT2. How can I accomplish this?

DT1:
Order ID, Serial No, Amount, Column x, Column z etc

DT2:
Select Order ID, Serial No, Sum(Amount) as Total
From DT1
Group by Order ID, Serial No

DT1:
Select * from DT1
left join DT2 on DT1.Order ID = DT2.Order ID and DT1.Serial No=DT2.Serial No

Any suggestions?
Br
Cris

1 Like
2

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

1 Like
3

@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

1 Like
4

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

//c

1 Like
5

@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…
Error_20180321

1 Like
6

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

2 Likes
7

Thanks @arivu96
Are the quotes the difference?

9

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…

Error_20180321_2

1 Like
10

@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

3 Likes
11

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

12

@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

1 Like
13

Hi @MAHESH1 @arivu96,

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

Result : 101 CA
103 CA
104 CA

Thanks

14

Hi @balu,
Try this code

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

Regards,
Arivu

2 Likes
15

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

16

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

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

Refer this one for DataTable operations

Regards,
Arivu

1 Like
17

Super bro :blush:
Working fine
Thanks @arivu96

1 Like
18

Hi @cristian_ivanoff,

Try this with less code.

Regards
Balamurugan

1 Like
19

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:

image

2 Likes
20

@arivu96, Hi,

Do you have a sample workflow that can be attached.

Thanks.

1 Like
21

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

1 Like