topic Re: Re: Re: Re: Converting Time Formats in QlikView

Converting Time Formats

mccook — Sun, 02 Nov 2014 17:42:45 GMT

Hi,

I have 2 fields, a start time and an end time and I want to work out the time taken, however the two fields are in different formats:

Start Time	End Time	Duration (Required)
13:45:22	140024	00:15:02

The field start time is formatted as hh:mm:ss however the end time is just a number, I believe I need to either convert the start time to a number or the end time to hh:mm:ss to then work out the duration.

Any advice would be appreciated?

Cheers,

Dean

Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 17:47:02 GMT

Interval(Time#([End Time], 'hhmmss')-Time#([Start Time], 'hh:mm:ss'),'hh:mm:ss') as Duration

Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 17:50:42 GMT

Re: Converting Time Formats

mccook — Sun, 02 Nov 2014 18:09:58 GMT

thanks, it doesn't seem to work for me, possibly because of the way I created the fields as below:

Date(Date,'hhmmss') as [End Time]

left(subfield(FileInfo,'\',10),6) as [Start Time]

Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 19:44:29 GMT

So your End Time is already a dual value.

Maybe

Interval([End Time]-Time#([Start Time], 'hh:mm:ss'),'hh:mm:ss') as Duration

works for you.

If not, please post your app with sample data.

thanks

regards

Marco

Re: Re: Converting Time Formats

mccook — Sun, 02 Nov 2014 20:31:10 GMT

Hi Marco,

Still no joy, I've attached a sample.

Cheers

Dean

Re: Re: Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 20:59:04 GMT

Hi,

if you really want seperate fields for date and time (instead of just formating the same field to show time in one case and date in the other), you could do like this:

QMC:
LOAD *,
    Interval([End Date Time]-[Start Date Time]) as Duration;
LOAD *,
    Timestamp([Start Date]+[Start Time]) as [Start Date Time];  
LOAD Date(Floor(Date)) as [End Date],
  Time(Frac(Date)) as [End Time],
  Timestamp(Date) as [End Date Time],
  SubField(SubField(FileInfo,'\',10),'-',2) as [Task],
  Time(Time#(Left(SubField(FileInfo,'\',10),6),'hhmmss')) as [Start Time],
  Date(Date#(SubField(FileInfo,'\',9),'YYYYMMDD')) as [Start Date]
FROM [S1 - QMC1.QVD](qvd);

I also added a combined date time field for start and end.

Are you sure about start and end sources, because the difference is negative?

To calculate the interval, you should always use full timestamps, because using only the time part could cause issues when the task runs from one day to another.

hope this helps

regards

Marco

Re: Re: Converting Time Formats

mccook — Sun, 02 Nov 2014 21:12:13 GMT

Perfect, I guess it makes sense that it needs the date included to work out duration.

Thanks,

Dean

Re: Re: Re: Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 21:20:43 GMT

Using only Timestamp fields for start and end:

QMC:
LOAD *,
    Interval([End Date Time]-[Start Date Time]) as Duration; 
LOAD Timestamp(Timestamp#(SubField(FileInfo,'\',9)&Left(SubField(FileInfo,'\',10),6),'YYYYMMDDhhmmss')) as [Start Date Time],
  Timestamp(Date) as [End Date Time],
  Trim(SubField(SubField(FileInfo,'\',10),'-',2)) as [Task]
FROM [S1 - QMC1.QVD](qvd);

with calculated date and time fields in the frontend:

hope this helps

regards

Marco

Re: Converting Time Formats

mccook — Sun, 02 Nov 2014 21:35:24 GMT

thanks again.

Re: Re: Re: Re: Converting Time Formats

MarcoWedel — Sun, 02 Nov 2014 22:03:33 GMT

You're welcome

regards

Marco