Filter Data table on multiple columns

Help Activities
, ,
1

Hi, I have a Data table with multiple columns
Col1 Col2 Col3
1 AAA 100
1 BBB 100
1 AAA 100
2 BBB 300
2 CCC 200
3 AAA 300
3 AAA 100

I need to get the data for each distict values of first two columns and get sum of the values in 3rd column in a new datatable. i.e

Col1 Col2 Col3
1 AAA 200
1 BBB 100
2 BBB 300
2 CCC 200
3 AAA 400

How to implement without using more for loops

Thanks

1 Like
2

Hi @MEGHA_cs

=> Read Range Workbook
image
Output-> dt
=> Use below syntax in Assign:

dt_Output = (From row In dt.AsEnumerable()
                   Group row By Key = New With {Key .Col1 = row.Field(Of Double)("Col1"), Key .Col2 = row.Field(Of String)("Col2")}
                   Into Group
                   Select dt.Clone().LoadDataRow({Key.Col1, Key.Col2, CDbl(Group.Sum(Function(g) g.Field(Of Double)("Col3")))}, False)).CopyToDataTable()

=> Write Range Workbook dt_Output
image

Regards

3

Thanks, but am getting this error

image
image477×583 133 KB

4

Hi @MEGHA_cs

dt_Output is of DataType System.Data.DataTable

If you have specific columns names specify that according to that I will modify the linq query

Regards

5

@MEGHA_cs

groupedDataTable = (From row In yourDataTable.AsEnumerable()
                    Group row By Key = New With { Key .Col1 = row.Field(Of Double)("Col1"), Key .Col2 = row.Field(Of String)("Col2") } Into Group
                    Let sumCol3 = Group.Sum(Function(r) Convert.ToInt32(r("Col3")))
                    Select yourDataTable.Clone().Rows.Add(Key.Col1, Key.Col2, sumCol3)).CopyToDataTable()

image
image1178×725 36 KB

image
image
Sequence4.zip (1.9 KB)

6

Hi @MEGHA_cs

According to the error you have faced below is the modified linq query:

dt_Output = (From row In dt.AsEnumerable()
                   Group row By Key = New With {Key .Country = row.Field(Of String)("Country"), Key .PMonth = row.Field(Of String)("PMonth"), Key .Dist = row.Field(Of String)("Dist")}
                   Into Group
                   Select dt.Clone().LoadDataRow({Key.Country, Key.PMonth, Key.Dist, CDbl(Group.Sum(Function(g) Convert.ToDouble(g.Field(Of String)("NAIJI"))))}, False)).CopyToDataTable()

Regards

7

Thanks, still am facing the error

image
image771×57 2.64 KB

8

Hi @MEGHA_cs

If possible can you share the excel file. I will help you with required changes

Regards

9

Sorry, cannot share file

10

Hi @MEGHA_cs

If possible atleast screenshot of the excel will help me correct the code.

Regards

11

Can you please help to filter based on distinct values in 1st 3 columns

12

Hi @MEGHA_cs

Try this:

dt_Output = (From row In dt.AsEnumerable()
             Group row By Key = New With {Key .Country = row.Field(Of String)("Country"), Key .PMonth = row.Field(Of String)("PMonth"), Key .Dist = row.Field(Of String)("Dist")}
             Into Group
             Select dt.Clone().LoadDataRow({Key.Country, Key.PMonth, Key.Dist, CDbl(Group.Sum(Function(g) Double.Parse(g.Field(Of String)("NAIJI"))))}, False)).CopyToDataTable()

Regards

13

Thanks, but still facing issue
image

14

Hi @MEGHA_cs

I will check and let you know. Give me some time.

Regards

15

Hi @MEGHA_cs

Try this query:

dt_Output = (From row In dt.AsEnumerable()
                              Group row By Key = New With {
                                  Key .Country = row.Field(Of String)("Country"),
                                  Key .PMonth = row.Field(Of String)("PMonth"),
                                  Key .Dist = row.Field(Of String)("Dist")
                              } Into Group
                              Let Area = Group.First().Field(Of String)("Area")
                              Let AAA = Group.First().Field(Of String)("AAA")
                              Let BBB = Group.First().Field(Of String)("BBB")
                              Let CCC = Group.First().Field(Of String)("CCC")
                              Let DDD = Group.First().Field(Of String)("DDD")
                              Let EEE = Group.First().Field(Of String)("EEE")
                              Select dt.Clone().Rows.Add(Key.PMonth, Area, Key.Dist, Key.Country, AAA, BBB, CCC, DDD, EEE, Group.Sum(Function(g) CDbl(g.Field(Of String)("NAIJI"))))).CopyToDataTable()

Regards

16

Hi, Can we use index instead of column names while filtering?

17

Hi @MEGHA_cs

Yes we can use the column index number’s also.

Regards

18

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