Group by - Data table


#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


#2

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


#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


#4

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

//c


#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


#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


#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


#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


#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


#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


#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


#17

Super bro :blush:
Working fine
Thanks @arivu96


#18

Hi @cristian_ivanoff,

Try this with less code.

Regards
Balamurugan


#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