Duration calculation

[qv11user]

Hello All,

I have a requirement where I will get data in new excel file for every 2 hours and it will replace the existing excel file.

Data.xlsx

Above files have orders information like Order ID, Status,Substatus, Time etc...
For exampe one Order 'OD1' is in 'In Transit' status at 9 AM.
the same order got damaged at 11 AM and status is changed to 'Damaged'.
the same order got replaced at 1 PM and status is changed to 'In Transit' again.
the same order deliverd at 3 PM and status is changed to 'Delivered'.

My requirement is to calculate the duration of status:
From 9 AM to 11 AM, the order is in 'In transit' status and the duration is : 2 hours
From 11 AM to 1 PM, the order is in 'Damaged' status and the duration is : 2 hours
From 1 PM to 3 PM, the order is in 'In transit' status and the duration is : 2 hours

At 3 PM order got delivered and we don't need to calculate the duration when the order is delivered.

I have attached the Excel file with sample data which also has my requirement in 'Requirement' sheet.

Can you help me on this?

[LRuCelver · Accepted solution]

Data_Raw:
NoConcatenate Load 
    "Order ID", 
    Status, 
    "Sub Status", 
    Timestamp(Timestamp#(Time, 'DD/MM/YYYY hh:mm')) as Time
Inline [
    Order ID,	Status,		Sub Status,	Time
    OD1,		In Transit,	In Transit,	19/02/2024 09:00
    OD1,		Damaged,	Exception,	19/02/2024 11:00
    OD1,		In Transit,	In Transit,	19/02/2024 13:00
    OD1,		Delivered,	Delivered,	19/02/2024 15:00
];

//	Data load starts here. The above is just for formatting the timestamps as such.
Data:
NoConcatenate Load
	RowNo() as RowID,
    *
Resident Data_Raw	//	Replace with your file.
Order By "Order ID", Time;

Left Join(Data) Load Distinct
	RowID - 1 as RowID,
    "Order ID",
    Time as NextTime
Resident Data;

DataFinal:
NoConcatenate Load
    "Order ID", 
    Status, 
    "Sub Status",
    Time,
    Interval(NextTime - Time, 'hh:mm') as Duration	// Use Interval() instead of Time() to support durations > 24h.
Resident Data;

Drop Tables Data, Data_Raw;