Group unique rows from Column A and add other sum rows of Column B

[tharani.natarajan]

Hi All,
I have a excel that contains Column Tracking Number ,SKU (string )and TotalOrder(int)

1.I need to find unique rows in Tracking Number
2. Append SKU
3.Add TotalOder

[ppr (Peter Preuss)]

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[tharani.natarajan]

Thanks ,I will try this

(From d In dtData.AsEnumerable
Group d By k=d(“TrackingNumber”).toString.Trim Into grp=Group
Let cs = grp.Sum(Function (rc) CInt( “0” & rc(“TotalOrder”).toString))
Let cn = String.Join(“;”, grp.Select(Function (rn) rn(“SKU”).toString).toArray)
Let ra = New Object(){k, cs, cn}
Select dtReport2.Rows.Add(ra)).CopyToDataTable

[ppr (Peter Preuss)]

@tharani.natarajan

Thanks ,I will try this

Perfect, this is a good first start

Let cs = grp.Sum(Function (rc) CInt( “0” & rc(“TotalOrder”).toString))

kindly note: the “0”& Trick will fail on negative numbers.

So we suggest the following:

Let cs = grp.Sum(Function (rc) CInt( rc("TotalOrder").toString.Trim))

And if blanks / non valid numbers are to expect handle it before / within once the Business Requirements are redefined and specifying it in detail

[tharani.natarajan]

@tharani.natarajan

(From d In dtData.AsEnumerable
Group d By k=d(“TrackingNumber”).toString.Trim Into grp=Group
Let cs = grp.Sum(Function (rc) CInt( “0” & rc(“TotalOrder”).toString))
Let cn = String.Join(“;”, grp.Select(Function (rn) rn(“SKU”).toString).toArray)
Let ra = New Object(){k, cs, cn}
Select dtReport2.Rows.Add(ra)).CopyToDataTable

What to give when we need a column after grouping without any changes ?

[ppr (Peter Preuss)]

@tharani.natarajan

please help to group by 2 columns

In general

Group d By k1=d(“TrackingNumber”).toString.Trim, k2=d(“ShippdedDate”).ToString.Trim Into grp=Group

for Dates we do also check if maybe a parsing into datetime before using it within the group definition could be a better approach. Datails on this depends also on the data source

[tharani.natarajan]

shipped Date is in “Date” format in source . Kindly help me with this .

Convert.ToDateTime(d(“ShippedDate”).ToString(“dd/mm/yy”) is not working

[ppr (Peter Preuss)]

@tharani.natarajan

shipped Date is in “Date” format in source . Kindly help me with this .

Convert.ToDateTime(d(“ShippedDate”).ToString(“dd/mm/yy”) is not working

As mentioned:

@ppr

for Dates we do also check if maybe a parsing into datetime before using it within the group definition could be a better approach. Datails on this depends also on the data source

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum