Duration between two timestamps for each day

[ahmed_qlik]

Hi

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

[Saravanan_Desingh]

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

[Ksrinivasan]

hi,

your sample result also wrong,

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

ksrinivasan

[ahmed_qlik]

Hi

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

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

[ahmed_qlik]

Hi

I have calculated Duration values manually.

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

B

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 

C

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

[Ksrinivasan]

hi,

try this link

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

ksrini

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output: