How to filter a date

[balanirmalkumar.s]

I have some input in excel. if any change in Total then need to pick date 1 from first record and date 2 from last record . refer below screenshot. How to achieve this.

[Michael.Vornes]

Hi, not sure if my understand is correct but below a suggestion of solution

Suggested Solution:

To achieve the result you’re looking for based on the change in the Total column, you can follow this approach using a DataTable and logic to detect changes in the “Total” values and output the desired results.

Steps:

1. Read the Excel Data:

• Use the Read Range activity to read the Excel data into a DataTable (e.g., dtInput).

2. Initialize Variables:

• Create a list of dictionaries to store the results, or use another DataTable for the expected output (e.g., dtOutput).
• Initialize variables to keep track of:
  • The first record date when a new total is found.
  • The last record date when the total changes or reaches the end.

3. Loop Through the DataTable:

• Use a For Each Row activity to iterate through dtInput.
• Track changes in the Total column. If the Total changes, capture the first date of the group (Date 1) and the last date of the group (Date 2).

4. Store the Output:

• Whenever a change in total occurs, add a new row to dtOutput with the first date, last date, and the total.

5. Write the Output:

• Once the loop completes, use the Write Range activity to export dtOutput back to an Excel file or display it in UiPath logs.

[sudster]

Here’s my solution

dtTest = (From d In dtTest.AsEnumerable
          Group d By r = d("ID").ToString Into grp = Group
          Select dtTest.Rows.Add({(grp.First)("Date 1"), (grp.Last)("Date 2"), (grp.Last)("Total")})
          ).CopyToDataTable

Note: The total 17.78 is missing from the expected output posted by the OP; not sure if this is an omission or requirement. If that is a requirement, please use the below LINQ:

dtTest = (From d In dtTest.AsEnumerable
          Group d By r = d("ID").ToString Into grp = Group
          Where grp.Count > 1
          Select dtTest.Rows.Add({(grp.First)("Date 1"), (grp.Last)("Date 2"), (grp.Last)("Total")})
          ).CopyToDataTable

It is possible to solve this with the first “Foe Each Row in Data Table” activity, but would require a another Data Table to collect the results; I chose to use LINQ to collect results into the same DT instead.
You can remove the Data Column “ID” at the end, if necessary.

[balanirmalkumar.s]

Can you share this workflow

[sudster]

Here’s the xaml file; please use your own Excel file for data:

DTGrouping.xaml (12.7 KB)

[balanirmalkumar.s]

I have one more column called “Average” . for that column also same logic. Need to check with both total and average column . if any variation in data. then need to pick start date and end date. How to modify in existing workflow.

[sudster]

That’s a very unusual data! If you want to group rows by the same total, their average would be the same value as the total, wouldn’t it? Basically you are doing:

y = (x * y)/x

If that’s the case, how can you have empty average rows?

[sudster]

@balanirmalkumar.s , thank you for marking the answer to your original query as the solution.

I understand the data you have is a rolling/moving average of 132 day blocks, which as you can see is a long term average already. Then having another average column probably is not very helpful. For graphing/charting etc, all you need is the consolidated totals output from my initial answer.

However, if you need to include the Average for some reason, please modify the solution in a couple of places as below:

sglPrevVal = If(String.IsNullOrWhiteSpace(currRow("Total").ToString), 0, CSng(currRow("Total"))) + If(String.IsNullOrWhiteSpace(currRow("Average").ToString), 0, CSng(currRow("Average")))

If(String.IsNullOrWhiteSpace(currRow("Total").ToString), 0, CSng(currRow("Total"))) + If(String.IsNullOrWhiteSpace(currRow("Average").ToString), 0, CSng(currRow("Average")))

(From d In dtTest.AsEnumerable
Group d By r = d("ID").ToString Into grp = Group
Select dtTest.Rows.Add({(grp.First)("Date 1"), (grp.Last)("Date 2"), (grp.Last)("Total"), (grp.Last)("Average")})
).CopyToDataTable

Hope this helps!