I need some support for a transformation. chatGPT couldn’t help here, but maybe you are smarter?
I have a list with working hours over the last few days. Every entry has a start time (column ‘Start’), which is rather ficticious, and a duration (col ‘Duration’), which is real.
Every day’s first record has its start time copied over to the repective field in ‘Start_day’. In the column ‘Start_min’ I convert ‘Start_day’ to minutes since midnight, and in ‘End_min’ I calculate the end of the task (Start_min + Duration_min) in minutes after midnight.
What I’d like to do now: fill out all emtpy fields in the columns ‘Start_min’ and ‘End_min’. Where Start_min is empty I’d like to copy the value from ‘End_min’ from the row above.
The only solution I see is to duplicate ‘End_min’ let’s say 10 times (because no day has more than 10 entries) and create an offset downward of 1,2,3,… and so on for these rows. Then I might set Start_min=End_min of one of the duplicated columns where Start_min is empty (via If- oder Javascript-transform). But maybe there is a better solution?
Has anyone of you an idea how this could be solved?
The first If and Total create a “Coup No”. The next If creates an End Help column holding the first End_min of the group and the durations of the next Duration_mins. With following Total you get the intended End_Min (End_min NEW). With this you can calculate a temporary Start_min value. The last If select the correct Start_min value in case it is the first value of the group it takes the original Start_min value otherwise it takes the calculated tmp value. The last three transformations are just removal of interim columns, reorder and rename of columns.
So the trick is to calculate the End_min first and go back to Start_min from there.
