load starttime and endtime as duration already in the loadscript(both not in the same row)

[Applicable88]

Hello,

i have problem working with a duration time, since the function becomes very long. when its not already loaded like a duration, its becomes hard to work with. 

For example this table:

AssignmentID	ActionID	Starttime	Endtime
1	1	8.00	08:30
1	2	08:30	09:45
1	3	09:45	10:15
1	4	10:15	11:15
1	5	11:15	12:00
2	1	09:00	09:22
2	2	09:22	12:00
3	1	12:00	12:10
3	2	12:10	12:40
3	3	12:40	12:33
3	4	12:33	12:45

For instance one actionID of a machine is the whole movement of a assignmentID to a robot. AssignmentID is unique, and all assignments are broken down in small steps ActionID's. 

So now I want to have the duration of one complete assignment, so means the smallest starttime and the biggest endtime of a Assignment ID. 

I can build a something for the table and also for barchart, but the load editor doesn't accept any aggr() function. Also datamanager cannot manage that for a calculated field. 

Hope someone has an answer.  Thank you in advance.

Best. 

[vhayward · Accepted solution]

Hej,

Maybe this piece of script would help with your issue, based on your input.

TMP_DataSet:
LOAD * INLINE [
AssignmentID, ActionID, Starttime, Endtime
1, 1, 08.00, 08:30
1, 2, 08:30, 09:45
1, 3, 09:45, 10:15
1, 4, 10:15, 11:15
1, 5, 11:15, 12:00
2, 1, 09:00, 09:22
2, 2, 09:22, 12:00
3, 1, 12:00, 12:10
3, 2, 12:10, 12:40
3, 3, 12:40, 12:33
3, 4, 12:33, 12:45
];

FINAL_DataSet:
NOCONCATENATE LOAD DISTINCT
[AssignmentID] AS [AssignmentID]
, TIME(MIN([Starttime]), 'hh:mm') AS [Assignment Starttime]
, TIME(MAX([Endtime]), 'hh:mm') AS [Assignment Endtime]
, TIME(MAX([Endtime]) - MIN([Starttime]), 'hh:mm') AS [Assignment Duration]
RESIDENT TMP_DataSet
GROUP BY [AssignmentID];
DROP TABLE TMP_DataSet;

Output:

Best regards,
Vincent Hayward