Data table Aggregation for dates

Help Studio
, ,
1

Hello Friends

i have a case i which i need to aggregate the dates based on the max date as per the below

Any suggestions

image

2

@m0e91

group the data on the first column (containing A, B…) as described here:

from the group take that row the max date. The max date can be found e.g. by a sorting. For comparing the dates e.g. a parsing with dateTime.ParseExact(… will help

1 Like
3

Thanks for your reply.

Can u support in this, as iam not so familiar with LINQ

Thanks in advance

4

@m0e91

give a try on following

Assign activity:
left side: dtGrouped - Datatype: DataTable
right side: YourDatatableVar.Clone

Assign activity:
left side: dtGrouped - Datatype: DataTable

(From d in YourDatatableVar.AsEnumerable
Group d by k=d(0).toString.Trim into grp=Group 
let mdt = grp.OrderBy(Function (x) CDate(x(1).toString)).Last()(1)
let ra = new Object(){k, mdt}
Select dtGrouped.Rows.Add(ra)).CopyToDataTable
  • It could be the case that the date conversion requires some adoptions
  • assumption: datatable is of 2 cols only
5

@ppr - Sorry , not try to hijack this post…I didn’t get the expected Result(Output shown below)…Am I missing something?

image
image1394Ă—618 28.8 KB

My Dt Setup

image
image711Ă—310 7.83 KB

1 Like
6

@prasath17
Many thanks for this cross check I did update the statement

let mdt = grp.OrderBy(Function (x) CDate(x(1).toString)).Last()(1)

bold part was ommited - we want to fetch the col value.

Thanks for cross checking and enhancement
GroupBy_1Col_TakeLastMonth.xaml (7.5 KB)

the original data was without leading 0 in the month part but both are working

1 Like
7

@ppr - Awesome …It worked…Thanks again…

Learning the LINQ truly from your forum replies and from the tutorials…

8

Thank my friend it worked

9

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