Excluding Non operational hours

[praveensai491]

Hi all,

i have Ticket reported date='12/12/2023 19:23:00' and Ticket resolved date=' 14/12/2023 10:33:00'

basically operation hours start from morning 6am to 23:59pm

if we calculate time difference between will get 35.33hr's between dates. I want to exclude non operational hours which is from 12:01 AM  to 5:59 AM hours need to exclude from total hours with respective dates

Example: from reported date 37min+4 hours=4:37hr's

13/12/2023 have 24hr's operational hours=18

14/12/2023 have 10:33hr- 6hrs non operational hrs(12:01 AM  to 5:59 AM)=4:33hrs

finally total operational hours b/w dates=26.7 hr's

pls help me how can i achieve this in qliksense

[LRuCelver]

According to your calculation, the example should have a total time of 27:10.

This is how you can calculate it in Qlik using preceding loads for better optimization:

Let vStartTime = Num(MakeTime(6, 0, 0));
Let vEndTime = Num(MakeTime(0, 0, 0)) + 1; // +1 for midnight the next day / 24:00

Data:
NoConcatenate Load
	TicketId,
    Reported,
    Resolved,
    Interval(RangeSum(Days, TimeAfterReported, TimeToResolved), 'hh:mm:ss') as Time;
Load
	*,
    $(vEndTime) - RangeMax(ReportedTime, $(vStartTime)) as TimeAfterReported,
    RangeMin(ResolvedTime, $(vEndTime)) - $(vStartTime) as TimeToResolved
    ;
Load
	*,
    (Floor(Resolved) - Ceil(Reported)) * ($(vEndTime) - $(vStartTime)) as Days,
    Reported - Floor(Reported) as ReportedTime,
    Resolved - Floor(Resolved) as ResolvedTime;
Load
	RecNo() as TicketId,
    Timestamp#(Reported, 'DD/MM/YYYY hh:mm:ss') as Reported,
    Timestamp#(Resolved, 'DD/MM/YYYY hh:mm:ss') as Resolved
Inline [
	Reported, Resolved
	'12/12/2023 19:23:00', '14/12/2023 10:33:00'
];

This can be reduced if you don't care for the other fields used or the ease of understanding for someone else:

Let vStartTime = Num(MakeTime(6, 0, 0));
Let vEndTime = Num(MakeTime(0, 0, 0)) + 1; // +1 for midnight the next day / 24:00

Data:
NoConcatenate Load
	*,
    Interval(RangeSum(
        (Floor(Resolved) - Ceil(Reported)) * ($(vEndTime) - $(vStartTime)), 
        $(vEndTime) - RangeMax(Reported - Floor(Reported), $(vStartTime)), 
        RangeMin(Resolved - Floor(Resolved), $(vEndTime)) - $(vStartTime)
    ), 'hh:mm:ss') as Time;
Load
	RecNo() as TicketId,
    Timestamp#(Reported, 'DD/MM/YYYY hh:mm:ss') as Reported,
    Timestamp#(Resolved, 'DD/MM/YYYY hh:mm:ss') as Resolved
Inline [
	Reported, Resolved
	'12/12/2023 19:23:00', '14/12/2023 10:33:00'
];

[praveensai491]

Hi LRuCelver,

Thanks for the solution you provide. which you provided sample data working perfect but when i replaced inline load with the real data from excel or database it populating same result of time for all records.

For your reference attaching the screenshots.

[LRuCelver · Accepted solution]

Your fields appear to already be formatted as a timestamp. You thus don't need the first step of transforming the texts loaded with the inline load to timestamps.

I couldn't test it, but try this:

Let vStartTime = Num(MakeTime(6, 0, 0));
Let vEndTime = Num(MakeTime(0, 0, 0)) + 1; // +1 for midnight the next day / 24:00

Data:
NoConcatenate Load
	"SR Reportdate",
    "Work Order Completion Date",
    Interval(RangeSum(
        (Floor("Work Order Completion Date") - Ceil("SR Reportdate")) * ($(vEndTime) - $(vStartTime)), 
        $(vEndTime) - RangeMax("SR Reportdate" - Floor("SR Reportdate"), $(vStartTime)), 
        RangeMin("Work Order Completion Date" - Floor("Work Order Completion Date"), $(vEndTime)) - $(vStartTime)
    ), 'hh:mm:ss') as Time
From [lib://AttachedFiles/Test data.xlsx]
(ooxml, embedded labels, table is Sheet2);

[praveensai491]

Hi Celver,

I have 3 tables actualstop,temprature and trip. these 3 tables connected by common field as trid_id.

in temprature table have fields timestamp,treatmentdate,temp_Car1,temp_Car2 wt i need is wn temprature in any one car more than 25 for the respective row see timestamp after that wr it normalizing temp means below25 we need to caculate time diff of two timestamps and sum all the duration.for more clarification im adding excel below.please suggest how to achieve solution.

ex: temp more at car2 at 1st row  timestamp=4/22/2024 4:03:12 PM

normalize at second row timestamp=4/22/2024 4:05:24 PM out will be=2.02min

same follows other calculations

script:

Actualstop:

LOAD

    ACTUAL_TIME,

    TRIP_ID

FROM [lib://AttachedFiles/BACSATS_20240422.xlsx]

(ooxml, embedded labels, table is actual_stops)where WildMatch(TRIP_ID,'2G12A1*','2G12B2*','2G12C2*','2G12C3*',

'2G12E1*','2F12A1*','2F12B2*','2F12C2*','2F12C3*','2F12E1*','2E12A1*','2E12B2*','2E12C2*','2E12C3*','2C32A1*',

'2C32B2*','2C32C2*','2C22A1*','2C22B2*','2A12G1*','2A12B2*','2A12C2*','2A12C3*','2A12E1*','2A12F1*','2B22C2*','2B22C3*',

'2B22E1*','2B22F1*','2B22G1*','2C22C3*','2C22E1*','2C22F1*','2C22G1*','2C32E1*','2C32F1*','2C32G1*','2E22F1*','2E22G1*'

);

left join(Actualstop)

//Concatenate

Trip:

LOAD

timestamp(TREATMENT_DATE) as Trip_TREATMENT_DATE,

    TRIP_ID,

    TRIP_TYPE

FROM [lib://AttachedFiles/BACSATS_20240422.xlsx]

(ooxml, embedded labels, table is trip)where TRIP_TYPE='revenue' and WildMatch(TRIP_ID,'2G12A1*','2G12B2*','2G12C2*','2G12C3*',

'2G12E1*','2F12A1*','2F12B2*','2F12C2*','2F12C3*','2F12E1*','2E12A1*','2E12B2*','2E12C2*','2E12C3*','2C32A1*',

'2C32B2*','2C32C2*','2C22A1*','2C22B2*','2A12G1*','2A12B2*','2A12C2*','2A12C3*','2A12E1*','2A12F1*','2B22C2*','2B22C3*',

'2B22E1*','2B22F1*','2B22G1*','2C22C3*','2C22E1*','2C22F1*','2C22G1*','2C32E1*','2C32F1*','2C32G1*','2E22F1*','2E22G1*'

);

 left join(Actualstop)

TrainEnv:

LOAD distinct TRIP_ID,

RowNo() as Rownum,

    TREATMENT_DATE as Temp_TREATMENT_DATE,

    day(TREATMENT_DATE)as Date,

    date(monthstart(TREATMENT_DATE),'MMM-YY') as A4period,

    timestamp("TIMESTAMP") as Temp_TIMESTAMP,

    hour(Timestamp("TIMESTAMP",'hh:mm:ss')) as TimeST,

    TRAIN_ID,

    TEMP_CAR_1,

    TEMP_CAR_2,

    if(TEMP_CAR_1>=25 or TEMP_CAR_2>=25,1,0) as result

FROM [lib://AttachedFiles/BACSATS_20240422.xlsx]

(ooxml, embedded labels, table is temperature) where WildMatch(TRIP_ID,'2G12A1*','2G12B2*','2G12C2*','2G12C3*',

'2G12E1*','2F12A1*','2F12B2*','2F12C2*','2F12C3*','2F12E1*','2E12A1*','2E12B2*','2E12C2*','2E12C3*','2C32A1*',

'2C32B2*','2C32C2*','2C22A1*','2C22B2*','2A12G1*','2A12B2*','2A12C2*','2A12C3*','2A12E1*','2A12F1*','2B22C2*','2B22C3*',

'2B22E1*','2B22F1*','2B22G1*','2C22C3*','2C22E1*','2C22F1*','2C22G1*','2C32E1*','2C32F1*','2C32G1*','2E22F1*','2E22G1*'

) ;

[praveensai491]

could anyone pls help scenario which i posted above