Compare a row(row1) with the row(row2) under. Then compare row2 with row3

[atomic]

Example:

I know how to do the first steps. Längd = time(in minutes in this case).

07:33:04 is the start time, to get the end time i need to add 61, I have done that and i get 08:34:04.

Next start time is 10:08:54, how can I make it so that i get the difference in this case 01:34:50 = 94.83 minutes for each row. Is it possible to loop through somehow and at the same time just do it for the same date(in this case 2020-01-02). When all the rows in the column “tid” that matches the same day has been processed I want it to then do the same thing for the next date(2020-01-04). Is this possible to achieve?

[ppr (Peter Preuss)]

@atomic
can you please the data as Excel file. With this we can faster setup a working prototype. Thanks

[atomic]

UiPathExampleTest.xlsx (9.6 KB)

[ppr (Peter Preuss)]

@atomic
Have a look on this first result:

I do feel that there still some smaller bugs. Once we do know the fails, then we will fix it in a next iteration

Demo XAML here:
atomic.xaml (16.8 KB)

[atomic]

Thank you so much man, I am currently trying to figure out how you did this and to understand the code you used. Do you know if it is possible to get the output into an excel file?

I imported system.globalization and added this to the xaml file: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference>

I will answer again after I have looked it through.

Thank you again

[ppr (Peter Preuss)]

while analysing the implementation, have a deeper look on:

• read range - ticked on preserve format - so the Längd data was readin in xx:xx:xx form instead to the fraction (I do refer to you other topic dealing with this)
• add data row - the IF function handling the nulls for the last column
• On result validation please have a special look on the minus value results. It seem to me that the running was longer as the next begin.

writing the result to Excel can be done with usin the result datatable and the write range activity

[atomic]

I will look into this, i will need some time to figure it out.

Could you explain this or/and link me where i can learn about how it works:

(From d In dtData.AsEnumerable
Group d By k=d(0) Into grp=Group
Select grp.toList).toList

Is it based on SQL?

and this one:

grp.OrderBy(Function (r) DateTime.ParseExact(r(0).toString.trim + " "+r(1).toString.Trim, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture)).toList

I’m especially wondering about the Function (r) part but if you can explain all.

[ppr (Peter Preuss)]

@atomic
Both statements a related to LINQ

Learn LINQ using Step-by-Step Tutorials

First Statement is about grouping:
Based on the First Column Info (Date) the data will be segmented into Lists of Datarows
Finally a list of all Lists of Datarows will be returned

Second Statement:
The Dateinformation from first Column along with the Time Information from second Column is converted into a DateTime and is be used to sort the DayGroup Datatrows Ascending

inside these function we do use Lambda functions. A lambda function is like a helper function. And with Function (r) we pass the datarow to the lambda function and can reference with r

[atomic]

Hi,

I am thinking if it is possible that in the same excel file(sample below): filter the “Utförare” column and then match “JA” column and then perform a calculation if the “JA” is more than 80% compared to the total count of the same “Person” under “Utförare”.

Currently I am doing it like this: I download a file that looks kinda like that one below. Then I sort the “Utförare” and remove the duplicates, after I handle each utförare as a transaction. I then download a file specific to the “Utförare” and I count all the rows and then count all the “JA”. This works for me but i wonder if i can achieve this in the same file with all the “persons” under “Utförare”. I feel like it is possible based at what you did with the other task above.

I Also wonder if its possible to ad the “Utförare” to the task above and then send each “FALSE” to a different excel file: With the columns, “Datum”, “Tid”, “Längd”, “FreeTime”, “TwoMinSlot” and also add the “Utförare” that is correct for the row. Also is it possible to make the “True” and “False” display something else then just “True” and “False” in an Excel-file, example if it is “False” it should instead write “Not Correct”.

Sorry for all my questions

This is a “sample”:

[ppr (Peter Preuss)]

@atomic
Let us do one thing.
As you started this topic with an initial question, you received a solution approach. So dont mix up iterations and requirement changes. Before stepping into a next iteration, lets proper close the current iteration.

So your request was implemented, what is the feedback on this from your end? Was this working as you expected?

Looking forward to your answer

[atomic]

Hi,

I am sorry, your solution for the first task was awesome. But if i mark that as the solution doesnt the post get locked?

[ppr (Peter Preuss)]

@atomic
not a problem as we can do next iteration within a new topic. benefit from this is One Topic, One Answer and after flagging the solution Researschers can faster easier check if it matching to heir case and what was the solution.

Fell free to reference me from new Topic and I will step in