topic Re: Duration between two timestamps for each day in App Development

Duration between two timestamps for each day

ahmed_qlik — Thu, 11 Feb 2021 10:10:09 GMT

I have to calculate Duration spend by vessel on berth based on vessel arrival and departure ( both are in timestamp format ) on Daily basis .

Sample Input :

vessel	arrival	departure
A	2/2/21 23:30	2/3/21 23:30
B	2/1/21 22:00	2/2/21 0:30
C	2/1/21 23:00	2/4/21 5:00

Expected Output:

Date	Duration
1-Feb-21	3 hours
2-Feb-21	25 hour
3-Feb-21	47 hours 30 minutes
4-Feb-21	5 hours

Thanks in advance.

@sunny_talwar @swuehl @hic @Michael_Tarallo

Regards

Ahmed

Re: Duration between two timestamps for each day

Saravanan_Desingh — Fri, 12 Feb 2021 01:35:11 GMT

Sorry, I can't understand. Can you explain me more? How did u get the Duration values?

Re: Duration between two timestamps for each day

Ksrinivasan — Fri, 12 Feb 2021 05:24:13 GMT

hi,

your sample result also wrong,

your time format in MM/DD/YYYY HH:MM'

ksrinivasan

Re: Duration between two timestamps for each day

ahmed_qlik — Sat, 13 Feb 2021 12:33:37 GMT

I can't understand how it is wrong... it is just sample data .

Yes, My date format is " MM/DD/YYYY hh:mm" .

Re: Duration between two timestamps for each day

ahmed_qlik — Sat, 13 Feb 2021 12:47:41 GMT

I have calculated Duration values manually.

For eg. if you see, on 1st feb, Vessel B was on berth between

2/1/21 22:00

2/2/21 0:30

which means 2 hours should be consider for 1st feb and 30 minutes for 2nd-Feb.

Same way, Vessel C was on berth between

2/1/21 23:00

2/4/21 5:00

which means 1 hour for 1st feb and 24 hours for2nd-Feb and24 hours for 3rd-Feb and5 hours for 4th-Feb.

So , total hours for 1st feb is (2+1) i,e 3 hours.

Hope I am able to explain the scenario.

Regards

M.A

Re: Duration between two timestamps for each day

Ksrinivasan — Sat, 13 Feb 2021 20:16:57 GMT

hi,

try this link

https://community.qlik.com/t5/Qlik-Sense-App-Development/determine-the-number-of-active-vehicles/m-p/1778590#M60499

ksrini

Re: Duration between two timestamps for each day

Saravanan_Desingh — Sun, 14 Feb 2021 02:27:54 GMT

Try this,

tab1: LOAD * INLINE [ vessel, arrival, departure A, 2/2/21 23:30, 2/3/21 23:30 B, 2/1/21 22:00, 2/2/21 0:30 C, 2/1/21 23:00, 2/4/21 5:00 ]; Left Join(tab1) LOAD *, Timestamp(Timestamp#(arrival,'M/D/YY hh:mm')+((IterNo()-1)/1440),'M/D/YY hh:mm') As Duration, 1 As Counter Resident tab1 While Timestamp#(arrival,'M/D/YY hh:mm')+((IterNo()-1)/1440) <= Timestamp#(departure,'M/D/YY hh:mm'); Left Join(tab1) LOAD *, Date(Floor(Duration)) As DtCounter Resident tab1;

Re: Duration between two timestamps for each day

Saravanan_Desingh — Sun, 14 Feb 2021 02:28:22 GMT

Output: