I’m currently going through the datatable course exercise and it requires me to group on the value of the month in the “date” column of every row, specifically, for every item, I need to check all items that were done on the same month and either sum up the values or create a new value.
So for this, I iterate through the whole datatable once, but since I’d need to check the month of every other item every time, I’m trying to avoid using a second for each row activity and use a filter table instead, but now I’m stuck as it seems to only take the whole column in the wizard.
Is there a way to use only the month value instead of the whole date column in the filter wizard ?
Use this linq
(From row In dt.AsEnumerable() Group row By m = row.Field(Of DateTime(“date”).Month Into g = Group Select result = New With { .Month = m, .Total = g.Sum(Function(r) r.Field(Of Int32)(“value”))}).ToList()
Add a new column called “Month” to your DataTable. Then use a For Each Row activity to assign the month value extracted from the “date” column to this new “Month” column. After that, use the Filter Data Table activity to filter rows based on the “Month” column. Finally, loop through the filtered rows to sum or process the values for each month.
dtResult = (From row In dtInput.AsEnumerable()
Group row By m = row.Field(Of DateTime)(“Date”).ToString(“MM-yyyy”) Into grp = Group
Select dtResult.LoadDataRow(New Object() {
m, grp.Sum(Function(r) r.Field(Of Double)(“Amount”))
}, False)).CopyToDataTable()
Thank you for the heads up !
I have no experience using linq overall, should I use this in an assign activity ? If not, how do I implement it with the rest of my workflow ?
Thank you in advance !
Thank you for the response, but that would add other loops through all of the items from my table, which is what I’m trying to avoid doing. Is there a way to filter it on the value’s property directly ?
Thanks in advance !
you need a full column to filter on. So without adding a helper column or using code, there isn’t a way to filter directly on the month part inside the activity.
Another Approach use LINQ:
Assume FinalOutput is a variable and type is List(Of Object)
FinalOutput = (From row In dt.AsEnumerable()
Group row By month = row.Field(Of DateTime)(“date”).Month Into Group
Select New With {
.Month = month,
.Total = Group.Sum(Function(r) r.Field(Of Double)(“value”))
}).ToList()
If helpful, mark as solution. Happy automation with UiPath
I’ve been trying the various linq formulas in this thread, but all somehow return errors after adapting the variable names. After adapting this first one, I get the following : “(From row In dt_Payments.AsEnumerable() Group row By m = row.Field(Of DateTime)(“Date”).ToString(“MM-yyyy”) Into grp = Group Select dt_Payments.Rows.Add(New Object() {m, grp.Sum(Function(r) r.Field(Of Double)(“Amount”))})).ToList()”, which tells me that the process cannot do the type cast from string to date required. My dt only contains object typed elements, and the dates are initially formatted as follows :
Is there something in the typing or in my adaptation of the formula that I am missing ?
Hey @Bastien_B you can try this linq
(From row In dt_Payments.AsEnumerable() Let d = DateTime.Parse(row.Field(Of String)(“Date”)) Group row By m = d.ToString(“MM-yyyy”) Into grp = Group Select dt_Payments.NewRow()).Tolist().ForEach( Sub(r) dt_Result.Rows.Add({m, grp.Sum(Function(x) Double.Parse(x(“Amount”).ToString()))}) End Sub)
From row In dt_Payments.AsEnumerable() Let d=DateTime.Parse(row(“Date”).ToString()) Group row By k=d.ToString(“MM-yyyy”) Into grp=Group Select resultDt.Rows.Add(k, grp.Sum(Function(x) Double.Parse(x(“Amount”).ToString()))).CopyToDataTable()
(From row In dt_Payments.AsEnumerable()
Let parsedDate = DateTime.ParseExact(row.Field(Of String)(“Date”), “dd-MM-yyyy”, System.Globalization.CultureInfo.InvariantCulture)
Group row By m = parsedDate.ToString(“MM-yyyy”) Into grp = Group
Select dt_Payments.Rows.Add(New Object() {m, grp.Sum(Function(r) Convert.ToDouble(r.Field(Of Object)(“Amount”)))})
).ToList()
Hello, this one seems to work better, but the string to date conversion fails, stating that the dates it’s provided with are not recognized as valid DateTimes, is there some format issue that I’m missing ?
(From row In dt_Payments.AsEnumerable() Let d = DateTime.Parse(row(“Date”).ToString()) Group row By m = d.ToString(“MM-yyyy”) Into g = Group Select resultDt.Rows.Add(m, g.Sum(Function(r) Convert.ToDouble(r(“Amount”))))).CopyToDataTable()
