Could this data loading script be improved to provide accurate end results?

[NenadV]

Hi, a code review would be highly appreciated.

This was generated by ChatGPT and it doesn't work correct. EndTime and consequently DurationMin (duration in minutes)  are not correctly calculated.

RawData:

load

Timestamp#(DateTime, 'DD.MM.YYYY hh:mm') as tmpDateTime,

    [Work Center],

    MachineStatusID

from MachineStatuses.xlsx (ooxml, embedded labels, table is Sheet1);

WithFlags:

load

    *,

    If([Work Center] = Peek('Work Center') and  MachineStatusID = Peek('MachineStatusID'), 0, 1) as ChangeFlag

resident RawData

order By [Work Center], MachineStatusID,  tmpDateTime;

drop table RawData;

WithGroups:

load

    *,

    rangesum(Peek('GroupID'), ChangeFlag) as GroupID

resident WithFlags

order by [Work Center], MachineStatusID, tmpDateTime;

MachineStatusesDuration:

load

    [Work Center],

    MachineStatusID,

    GroupID,

    min(tmpDateTime) as StartTime,

    max(tmpDateTime) as EndTime,

    interval(max(tmpDateTime) - min(tmpDateTime), 'hh:mm:ss') * 1440  as DurationMin

resident WithGroups

group by [Work Center], MachineStatusID, GroupID;

drop tables WithFlags, WithGroups;

Thank you

[marcus_sommer]

I assume that you want to calculate the duration between the timestamps of the Work Center. The approach to create the GroupID and to aggregate the timestamps against it may in general a working one but it would require to remove the MachineStatusID from the aggregation.

Personally I wouldn't do it in this way else using the interrecord-functions of peek() and previous() to create from the timestamp directly two fields - start + end. Means similar like the method by the ChangeFlag the previous timestamp is used as end and the current value as start - you need only to sort the timestamp with desc.

Depending on the goal it might be useful to repeat the logic in asc and desc and with the filtering against certain states respectively their changes.

Don't try everything at one else doing it in n small steps with a limited data-set shows very quickly if start and end information are the wanted ones and what happens if the order- and/or the if-conditions are a bit adjusted. Further helpful would be to add recno() + rowno() to the loads to track the way from the source to the target.

On top of such measurements may come some aggregations to get the min/max/count values against n unknown records of Work Center and states.   

[NenadV]

Thanks @marcus_sommer  I need to calculate duration for each work center and machineStatus.

[marcus_sommer]

Then go with the mentioned logic to get a start + end field with a conditional approach like the ChangeFlag - just keep attention on the sort order to sort the timestamp descending.

[NenadV · Accepted solution]

@marcus_sommer FYI ChatGPT has helped me to get what I need using this a little reworked data loading script:

SortedData:
LOAD
 [Work Center],
 MachineStatusID,
 DateTime as StartDateTime
from [..\ConsolidatedExcel\MachineStatuses.xlsx] (ooxml, embedded labels, table is Sheet1)
ORDER BY [Work Center], MachineStatusID, DateTime; // this is not correct syntax so I had to re- work it   little  by using one temporary table and apply order by in a load . . . resident statement

FinalData:
LOAD
[Work Center],
MachineStatusID,
StartDateTime,
If(
[Work Center] = Peek([Work Center]) and MachineStatusID = Peek(MachineStatusID),
Peek(StartDateTime)
) as EndDateTime,
If(
[Work Center] = Peek([Work Center]) and MachineStatusID = Peek(MachineStatusID),
(Peek(StartDateTime) - StartDateTime) * 1440
) as DurationMinutes
RESIDENT SortedData
ORDER BY [Work Center], MachineStatusID, StartDateTime DESC;

DROP TABLE SortedData;