Assign dates to the datatable

[SunnyJha]

Hi, i have a datatable dt in which as per the price change in unit cost column the dates needs to be updated and the rows needs to be modified.

In the example below, the price change in Unit Cost column occurs at 2 places, D6 & D11. And accordingly you can see the output hats needed.
If there is no price change then only 1 row should be there as per the logic.

i/p dt:

o/p dt:

[ppr (Peter Preuss)]

Assign Activity:
dtResult =

(From d in dtData.AsEnumerable
Group d by k = d("Unit Cost").toString.Trim into grp=Group
Select r = grp.First()).CopyToDataTable

Please ensure that you also mark solutions when answers are given to you and are working (this and your other topics)

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has found…

[ppr (Peter Preuss)]

@ppr

dtResult =

(From d in dtData.AsEnumerable
Group d by k = d("Unit Cost").toString.Trim into grp=Group
Select r = grp.First()).CopyToDataTable

maybe you are also looking for taking the first start and the last end:

Assign Activity
dtResult = dtData.Clone

Assign Activity:
dtResult =

(From d in dtData.AsEnumerable
Group d by k = d("Unit Cost").toString.Trim into grp=Group
Let ra = new Object(){grp.First()(0), grp.First()(1), grp.Last()(2), k}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

[SunnyJha]

your solution does the grouping but I do have other columns present as well which are coming as empty. And the position of the columns shown above is not exactly as shown.
I have just shown 4 out of the 11 columns that I am dealing with.

[ppr (Peter Preuss)]

feedabck is not clear, had you tried second LINQ? Otherwise let us know the exact deviation

[SunnyJha]

Hi @ppr,
I used your second query and modified it as -
(From d In dt_Final.AsEnumerable
Group d By k = d(“Unit Cost”).toString.Trim Into grp=Group
Let ra = New Object(){grp.First()(0), grp.First()(1), grp.Last()(2), grp.Last()(3),grp.Last()(4),grp.Last()(5),
grp.Last()(6),k, grp.Last()(8), grp.Last()(9),grp.Last()(10)}
Select r = out_dt_Final.Rows.Add(ra)).CopyToDataTable

I have 11 columns in my datatable.
It does the work of grouping them but as per my original query, I also want to modify the dates.
After grouping, the start date should be the first occurence and the end date should be then modified to the last occurence of the group.

i/p:-

o/p:-

[SunnyJha]

I updated it to the following query-

(From d In dt_Final.AsEnumerable
Group d By k = d(“Unit Cost”).toString.Trim Into grp=Group
Let ra = New Object(){grp.First()(0), grp.First()(1), grp.First()(2), grp.First()(3),grp.First()(4),grp.Last()(5),
grp.First()(6),k, grp.Last()(8), grp.First()(9),grp.First()(10)}
Select r = out_dt_Final.Rows.Add(ra)).CopyToDataTable

This worked

[ppr (Peter Preuss)]

@SunnyJha

It does the work of grouping them but as per my original query, I also want to modify the dates.
After grouping, the start date should be the first occurence and the end date should be then modified to the last occurence of the group.

in general it looks good.
we can optimize your code on

(From d In dt_Final.AsEnumerable
Group d By k = d(“Unit Cost”).toString.Trim Into grp=Group
Let ra1 = New Object(){grp.First()(0), grp.First()(1)}
Let ra2 = grp.Last().ItemArray.Skip(2)
Let ra = ra1.Concat(ra2).Cast(Of Object).ToArray()
Select r = out_dt_Final.Rows.Add(ra)).CopyToDataTable

so the LINQ should work as expected (checking when taking first, last group member)