Hi team, I’m having a excel file which has dates and names. Using Name I’m getting the number of rows. Now, further I want to filter based on each date. Attaching excel for reference. In the below Excel, would like to get the count for 5/27/2021 as 5 and 5/28/2021 as 2 and write it in Sheet2. Can someone please help me to solve this?
DateCount.xlsx (8.4 KB)
Can be done with a group by. Have a look here:
In your scenario:
prepare a datatable with the target Column structure:
Name, Date, Count
with the help of build datatable activity - Variable: dtResult
use an assign acitivty:
left side: dtResult
right side:
(From d in YourOriginDataTableVar.AsEnumerable
Group d by k1=d(0).toString.Trim, k2=d(1).toString.Substring(0,10) into grp=Group
Let ra = new Object(){k1, grp.First()(1), grp.Count}
Select dtResult.Rows.Add(ra)).CopyToDataTable
Hi @Palaniyappan Thanks for the quick response!
In my scenario i don’t have the date value to compare with, the excel will have different set of dates in each execution and for each date I have to get the count (number of rows with same date)
Hi @ppr Thanks for the detailed explanation!
I tried to assign the newly created DT variable to given expression and I’m getting the below compiler error. Attaching screenshot for reference
give a try on following:
(From d in YourOriginDataTableVar.AsEnumerable
Group d by k1=d(0).toString.Trim, k2=d(1).toString.Substring(0,10) into grp=Group
Let ra = new Object(){k1, grp.First()(1), grp.Count}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
Also check the difference yellow marked:
Getting runtime exception “Assign: Input array is longer than the number of columns in this table.” Attaching xaml for reference
FileOps.zip (2.9 KB)
Exception means: it is added rowArray has more cols as the used datatable
Is following ensured?
Great sir! I missed to mention the output dt from Build dt activity which lead to error. Now got the result as expected!
Can you please help me to modify the column index as the actual columns in my scenario are B and C (Name and Date)
just replace the columnidex by columnname sourrounded by " eg. d(“Name”)
Columnname is depending (assumed data comes from Excel) from the add headers setting
1 Like
Thank you so much!! completely a new learning
For additional learning also have a look here:
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.