topic Re: Excluding Non operational hours in App Development

Excluding Non operational hours

praveensai491 — Fri, 15 Nov 2024 21:04:57 GMT

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

Re: Excluding Non operational hours

LRuCelver — Mon, 05 Feb 2024 15:30:14 GMT

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' ];

Re: Excluding Non operational hours

praveensai491 — Wed, 07 Feb 2024 12:12:26 GMT

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.

Re: Excluding Non operational hours

LRuCelver — Wed, 07 Feb 2024 15:09:49 GMT

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);

Re: Excluding Non operational hours

praveensai491 — Mon, 06 May 2024 06:12:15 GMT

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*'

) ;

Re: Excluding Non operational hours

praveensai491 — Mon, 06 May 2024 11:02:46 GMT

could anyone pls help scenario which i posted above