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

Applicable88 — Wed, 20 Jan 2021 19:43:26 GMT

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.

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

vhayward — Wed, 20 Jan 2021 19:55:21 GMT

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

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

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

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