Group By and Count with several conditions

Help Studio
, ,
1

Hi there,
How can I perform group by and count the return records in a datatable according to the condition below using linq query?

dt.[Column1] = dt.[Column4] AND dt.[Column2] = dt.[Column5] AND dt.[Column3] = dt.[Column6]

2

hi @ppr is this something you could help with?

3

can you paste some sample data. Thanks

4

SampleGroupBy.xlsx (10.0 KB)
Attached the sample data and expected output. As you can see Paycheck Data is not in the correct format, I need to format it as well.

Requirement group and count ID by Paycheck Date = Payment Date, Start Date = Period Start Date, End Date = Period End Date of the same row

5

@niro
give a try on following

prepare the target datatable with build datatable activity - dtResult
Columns: Group, Count

use an assign activity
left side: dtResult
right side:

(From d in dtData.AsEnumerable
Group d by k=d("ID").toString.Trim into grp=Group
let grs = (From g in grp
               Where CDate(g(1).toString).toString("dd.MM.yyyy").equals(g(4).toString)
               Where g(2).toString.Equals(g(5).toString)
               Where g(3).toString.Equals(g(6).toString)
               Select dr=g).toList
Let ra = new Object(){k,grs.Count}
Select dtResult.Rows.add(ra)).CopyToDataTable

also have a look here:

the non LINQ approach maybe also can help to better handle the different date formatings

6

thanks a lot. it works, the data is from left join, any recommendation to handle empty date for CDate(g(1).toString).toString(“dd.MM.yyyy”) as it fails if date is empty? something that i could handle in the same query like give a dummy date?

7

different approaches to handle it

  • filtering out the row if it is a non valid date
  • using a default date for non valid dates

Just let us know what will be the best fit for your case. In case of dummy date which value should it have?

9

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