A tricky one: Allocate Time in Time periodes (Start DateTime / End DateTime) to specific days

[Chris_K]

Hello…

I am facing the problem to allocate the used time to the days on which the work was done

The following table should illustrate the way the data are provides and the second table should show the expected output

Data source

ID	start date	start time	end date	end time	Typ
A	01.10.2019	10:00	02.10.2019	16:00	Work
A	02.10.2019	18:00	02.10.2019	19:00	Work
A	02.10.2019	22:00	02.10.2019	23:00	Work
A	04.10.2019	08:00	04.10.2019	13:00	Work

Structure expected

ID	Date	Hours	Typ
A	01.10.2019	10	n/a
A	01.10.2019	14	Work
A	02.10.2019	16	Work
A	02.10.2019	2	n/a
A	02.10.2019	1	Work
A	02.10.2019	3	n/a
A	02.10.2019	1	Work
A	02.10.2019	1	n/a
A	03.10.2019	24	n/a

A	04.10.2019	8	n/a
A	04.10.2019	5	Work
A	04.10.2019	11	n/a

As you can see, the data set are sometimes across several days
Sometime multiple per day
And the unused time of a day (or a hole unused day) should be characterised as well

Actually I think a loop on a min/max date temp Table should be the leading table, and inside the loop there has to be a loop over the entries of a day. But frankly spoken, I need some help since I’m not the programming hero

Any help would be yery welcome

All the best

Chris

[marcus_sommer]

I must admit that I could not comprehend the logic how the Hours from your expected ouput should be calculated and how Type should be categorized - I think you need additionally logic how to determine the unused time or days. Nevertheless I think the following will be useful as a starting point to create additionally records and to calculate the hours:

load *, rowno() as RowNo;
load *, interval(if(IterNo = 1 and [start date] + IterNo - 1 = [end date], [end time] - [start time],
if(IterNo > 1 and [start date] + IterNo - 1 < [end date], 1,
if(IterNo > 1 and [start date] + IterNo - 1 = [end date], [end time], 1 - [start time]))), 'hh') as Hours;
load *, recno() as RecNo, iterno() as IterNo inline [
ID start date start time end date end time Typ
A 01.10.2019 10:00 03.10.2019 16:00 Work
A 02.10.2019 18:00 02.10.2019 19:00 Work
A 02.10.2019 22:00 02.10.2019 23:00 Work
A 04.10.2019 08:00 04.10.2019 13:00 Work
] (txt, delimiter is \t) while [start date] + iterno() - 1 <= [end date];

- Marcus

[Chris_K]

Marcus, thanks a lot...but

Unfortunately your kick of into the solution does not enlighten me 😉

I think the approach could be to generate first a time table  from min to max date
Then loop through the days, try to catch an entry
Then check :

if exist data set at loop date and end date > start date
=> create an data set with hours = 24- start time
=> and create data set for all days between start date (loop date)  and end date with 24 hours if date < end date and with end time as hours for the end date data sets

If exist and end date = actual loop date (start date)
=> create an data set with hours = end time – start time

The result above has then to be checked again in the next loop on same time data if there are now days/time 
• If non exist an data set at loop date => create an 24Hours with n/a 
• And if sum of used time of a day is less than 24 hours => create an 24Hours – used time n/a data set

That’s my thoughts, but how to write that down???

[marcus_sommer]

My suggestion contained a loop:

… while [start date] + iterno() - 1 <= [end date] ...

on the date-level and creates a record for each date between start and end. Not included is a further loop to the hours because it's not necessary to calculate the duration per date respectively per record and would make the matter rather more complex instead of to be helpful.

But it depends on your further requirements if it may be useful to create a bigger table with all dates and times and associate them with appropriate master calendars + master timetables and also setting flags to calculate with them. Of course it's possible but you should have a good reason to go this way. If you only need to get the pure duration from start to end you won't even need to create additionally records ...

Beside this just start with the first load-statement and these dummy-data from my suggestion and put the result into a tablebox and see what's happens - after that comment on the next statement … I'm sure you will comprehend the logic and be able to adjust the duration-calculation like you need them and the same with the not included state of the records.

- Marcus

[Brett_Bleess]

If Marcus' last post still left you wondering what to do, check out the Design Blog area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

You may be able to find something there as well to help out.  If you did use Marcus' info, please be sure to mark that as the solution, or let us know it did solve things, and we can mark it accordingly, so others know it worked.

Regards,
Brett