Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shakeeb_mohammed
Contributor III
Contributor III

Timestamp function

Hi,

Could someone help me with the following script please -

if(Timestamp([Confirmed Arrival Time])>Timestamp([PTA]),1,0) as LatePTA,

     if(Timestamp([Confirmed Arrival Time])<Timestamp([PTA]),1,0) as OnTimePTA,

I'm trying to add a statement that will say if the confirmed arrival time is more or less than 30 minutes of the PTA mark it as OnTimePTA

I am using Qliksense - Thanks in advance!

10 Replies
mgomezlor
Contributor III
Contributor III

May be like this

     if(Timestamp([Confirmed Arrival Time])<Timestamp([PTA]) and Timestamp([PTA]) - Timestamp([Confirmed Arrival Time]) <= MakeTime(0, 30),1,0) as OnTimePTA,

shakeeb_mohammed
Contributor III
Contributor III
Author

Hi,

Thanks for the reply.

Unfortunately this doesn't work, the below is the 2 timestamps that calculate the status but currently in Qlik it still recognises this as a Late because the actual arrival time is late than the Planned time .

Data File.JPG

ToniKautto
Employee
Employee

Does Timestamp([Confirmed Arrival Time]) and Timestamp([PTA]) return the timestamp values that you expect?

If not, you need to format the incoming value properly. Please confirm what format your data source has for theses fields.

If these work, then you do not need to use the timestamp format functions, as the comparison is don the on the timestamps existing numerical representation.

Time is defined as a fraction of a day. There are 24 hours in a day, and each hour contains of 60 minutes. One minute is there for 1/(24*60) = 1/1440, meaning that 30 minutes is 30/1440.

Adjusting a timestamp or time is simply done by adding or subtracting time. The below comparison would return true if [Confirmed Arrival Time] is larger than PTA and smaller than or equal to PTA+30 min. Is this what you are asking for?

[Confirmed Arrival Time] > [PTA] AND [Confirmed Arrival Time] <= [PTA]+(30/1440)

shakeeb_mohammed
Contributor III
Contributor III
Author

Hi,

i had tried the following -

  [Confirmed Arrival Time] > [PTA] AND [Confirmed Arrival Time] <= [PTA]+(30/1440) as OnTimePTA2,

  [Confirmed Arrival Time] < [PTA] AND [Confirmed Arrival Time] >= [PTA]-(30/1440) as LatePTA2,

But no luck ,

The fields Confirmed time arrival and PTA are formatted to return the correct timestamp.

PTA,

     Date(left(PTA,10))as PlanDate,

     WeekStart(Date(left(PTA,10))) as Week,

     Timestamp(PTA) as TS_PTA,

     Timestamp([Confirmed Arrival Time]) as TS_ConfirmedArrivalTime,

I may not explained the question properly.

I'm trying to add a statement that will say if the confirmed arrival time is more or less than 30 minutes of the PTA mark it as OnTimePTA –

and if the Confirmed time arrival + 30mins is more than the PTA mark it as LatePTA.

E.g. – Planned time arrival is 13:00 – arrives at 13:15 but because this is within the 30 minute window it will still be on time. But if it arrives at 13:35 it will mark it as Late.

mgomezlor
Contributor III
Contributor III

May be like this

     if(Timestamp([Confirmed Arrival Time]) -Timestamp([PTA]) <= MakeTime(0, 30) or Timestamp([Confirmed Arrival Time])<Timestamp([PTA]),1,0) as OnTimePTA,

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Are you able to upload a sample data. Specifically your [Confirmed Arrival Time] and PTA

This will help to see format for the timestamp


Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Are you able to upload a sample data. Specifically your [Confirmed Arrival Time] and PTA

This will help to see format for the timestamp


shakeeb_mohammed
Contributor III
Contributor III
Author

Thank you all for the help!

I've managed to get this resolved.

The Script i used was -

if(num#(interval(Timestamp([Confirmed Arrival Time],'hh:mm:ss') - Timestamp([PTA],'hh:mm:ss'),'m')) <= 30,1,0) as OnTimePTA,

if(num#(interval(Timestamp([Confirmed Arrival Time],'hh:mm:ss') - Timestamp([PTA],'hh:mm:ss'),'m')) > 30,1,0) as LatePTA

ToniKautto
Employee
Employee

Thanks for clarifying the context. Would this give you the expected flags?

[Confirmed Arrival Time] <= [PTA]+(30/(24*60)) AS OnTime

[Confirmed Arrival Time] > [PTA]+(30/(24*60)) AS Late