Minutes between timestamps distribute per hour

[Qliksense_77]

Hi,

I have data with timestamps for start and end. I want to calculate the minutes between the timestamps and distribute per hour.

Anybody that can help?

My data:
Start_timestamp	End_timestamp
04/02/2022 12.55.00	04/02/2022 15.25.00
Desired output:
DateTime	Minutes
04/02/2022 12	5
04/02/2022 13	60
04/02/2022 14	60
04/02/2022 15	25

[vinieme12]

use Interval()

Interval(Timestamp#(End_timestamp,'dd/mm/yyyy hh.mm.ss')-Timestamp#(Start_timestamp,'dd/mm/yyyy hh.mm.ss'),'mm') as minutes_diff

[Qliksense_77]

@vinieme12  thanks for the reply and sorry for my bad english.

My problem is not calculating the minutes between the timestamps. 

I want to calculate the minutes between the timestamps per hour. For instans at hour 15 there are 25 minutes between the timestamps. And at hour 14 there are 60 minutes between the timestamps and so on.

[Or]

You would likely need to use Load ... While and IterNo() to create a record for each hour ((IterNo()-1)/24) should be the means to add hours to the starttime. For each hour, you'll need to calculate the lesser of Endtime minus that hour, or 60, which you should be able to do using RangeMin().

[Qliksense_77]

@Or , thanks for the reply.

Can you write the load script 🙂 

I cant get this to work.

[Qliksense_77]

Anybody that can help ? 

[Padma123]

please try it once hope it is helpful...

data:

load *,Hour(Start_timestamp) as starthour,

Minute(Start_timestamp) as startmin,

Hour(End_timestamp) as endhour,

Minute(End_timestamp) as endmin,

Second(Start_timestamp) as startsec;

load Timestamp#(Start_timestamp,'DD/MM/YYYY hh.mm.ss')as Start_timestamp,

Timestamp#(End_timestamp,'DD/MM/YYYY hh.mm.ss') as End_timestamp;

load * inline [

Start_timestamp End_timestamp

04/02/2022 12.55.00 04/02/2022 15.25.00 

](delimiter is '\t');

let vstart=Peek('starthour');

let vend=Peek('endhour');

let vendmin=Peek('endmin');

join

final:

load $(vstart)+IterNo()-1 as starthour

AutoGenerate 1

while $(vstart)+IterNo()-1<=$(vend);

NoConcatenate

final2:

load 

If(IsNull(startmin) or IsNull(startsec),MakeTime(starthour),

MakeTime(starthour,startmin,startsec)) as [Date Time],

    If(IsNull(startmin) or IsNull(startsec),if(starthour<>$(vend),60,$(vendmin)),

    60-startmin) as minutes

Resident data;

drop table data;

[Qliksense_77]

thanks @Padma123 , it looks promising 🙂

I also need the date in the field DateTime, because sometimes the start timestamp and the end timestamp have different dates.

Do you also have a solution for that  ?

[Padma123 · Accepted solution]

for date you can use expression in final table

if(IsNull(Start_timestampdate),Peek(Start_timestampdate),Start_timestampdate) as Start_timestampdate

after for timestamp use 

Timestamp(Start_timestampdate+time,'DD/MM/YYYY hh.mm.ss')

[Qliksense_77]

@Padma123 , awesome, thank you:)