Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

qvraj123
Contributor II

how to get minutes from..

Hi All - Thank you for your time,

I have a dataset like starttime  YYYYMMDD hh:mm:ss and endtime YYYYMMDD hh:mm:ss

there are some jobs which start in the night at 11.45PM then complete at 12.30AM - in this scenario how to get the total number of minutes for that particular job lets say in this case JOB XYZ

any suggestions - thanks for your help

Raj

1 Solution

Accepted Solutions
MVP
MVP

Re: how to get minutes from..

if you add in a text box (or chart or load if you replace with your fields)

=interval(

timestamp#('20150512 12:30:00 AM', 'YYYYMMDD hh:mm:ss tt') - timestamp#('20150511 11:45:00 PM', 'YYYYMMDD hh:mm:ss tt')

, 'mm')

you get 45 min

12 Replies

Re: how to get minutes from..

Interval(EndTime-StartTime,'mm') as Duration

qvraj123
Contributor II

Re: how to get minutes from..

DataTesting:

LOAD * INLINE [

    JOBNAME, STARTTIME, ENDTIME
    XYZ, 20150511 11:45:05, 20150512 12:32:05
    ABC, 20150511 11:46:08, 20150512 12:33:08
    WBC, 20150511 11:47:10, 20150512 12:34:10
    AAA, 20150511 11:57:56, 20150512 12:45:09
]
;
Final:
LOAD
JOBNAME AS Job,Interval(Timestamp#(ENDTIME,'YYYYMMDD hh:mm:ss')-Timestamp#(STARTTIME,'YYYYMMDD hh:mm:ss'),'mm') AS Time_Taken
Resident DataTesting;

Hi Manish, tried this but it is not giving me the right answer - thanks for your time

Raj


MVP
MVP

Re: how to get minutes from..

if you add in a text box (or chart or load if you replace with your fields)

=interval(

timestamp#('20150512 12:30:00 AM', 'YYYYMMDD hh:mm:ss tt') - timestamp#('20150511 11:45:00 PM', 'YYYYMMDD hh:mm:ss tt')

, 'mm')

you get 45 min

MVP
MVP

Re: how to get minutes from..

LOAD *,

interval(

timestamp#(ENDTIME, 'YYYYMMDD hh:mm:ss') - timestamp#(STARTTIME, 'YYYYMMDD hh:mm:ss')

, 'hh mm ss')           // or 'mm' for min

INLINE [

    JOBNAME, STARTTIME, ENDTIME

    XYZ, 20150511 11:45:05, 20150512 12:32:05

    ABC, 20150511 11:46:08, 20150512 12:33:08

    WBC, 20150511 11:47:10, 20150512 12:34:10

    AAA, 20150511 11:57:56, 20150512 12:45:09

];

1.png

qvraj123
Contributor II

Re: how to get minutes from..

Thani Massimo - This is working

(Timestamp#(ENDTIME,'YYYYMMDD hh:mm:ss tt')-Timestamp#(STARTTIME,'YYYYMMDD hh:mm:ss tt'),'hh:mm:ss') AS Time_Taken


- I missed tt - what does this mean?

mov
Esteemed Contributor III

Re: how to get minutes from..

I tried, the results 1487 min - looks about right to me (?)

MVP
MVP

Re: how to get minutes from..

copied from Qlik help (search tt)

Times

  • To describe the hours, use the symbol "h" for each digit. 
  • To describe the minutes, use the symbol "m" for each digit. 
  • To describe the seconds, use the symbol "s" for each digit. 
  • To describe the fractions of a second, use the symbol "f" for each digit. 
  • To describe the time in AM/PM format, use the symbol "tt" after the time. 
  • Arbitrary separators can be used.
qvraj123
Contributor II

Re: how to get minutes from..

Hi Massimo, but I do not have AM/PM in my source does this mean I need to have these like below in order to get the correct results - Thanks again for your time

JOBNAME, STARTTIME, ENDTIME

XYZ, 20150511 11:45:05 PM, 20150512 12:32:05 AM

Raj

Highlighted
qvraj123
Contributor II

Re: how to get minutes from..

Hi Mike,

how can this be right - 20150511 11:45:05 20150512 12:32:05  - we need to get the total number of minutes from this

Thanks for your time

raj

Community Browser