topic converting a data string into time in QlikView

converting a data string into time

Fri, 29 Jul 2011 18:13:34 GMT

hi,

I am having trouble with trying to format my data into a time column. currently, my database returns and time value of ex: 0538AM meaning 5:38 AM. i am doing an analysis based on time and i need to convert my data into somthing that is usable, preferably in the form of hh:mm.

I have tried using the following functions: time, time#, num#, num, timestamp, and timestamp# to give me usable data but it only seems to return null or 12:00 AM values. an example of the code i have been trying is: time(num#(mid(time1,1,4))). when i use the code without the time function in front i get results like this: 0538.

Any suggestions?

Thanks so much

converting a data string into time

swuehl — Fri, 29 Jul 2011 18:21:15 GMT

You can use time() function to format a time / date field.

But you still want to get one, so I think you should try:

time#(mid(time1,1,4),'hhmm')

or time#(time1,'hhmmtt')

Then you can format this to e.g. hh:mm format

time(time#(time1,'hhmmtt'),'hh:mm')

{hhmmtt should be the correct format code for your input, please refer to and check the format code page in the help}

Regards,

Stefan

converting a data string into time

Fri, 29 Jul 2011 19:03:51 GMT

Hi Stefan

I have tried that but it still gives me the same problem were all the times returned are 12:00:00 AM.

Thanks for the suggestion

Re: converting a data string into time

nagaiank — Fri, 29 Jul 2011 19:42:01 GMT

The solution given be Stefan worked for me.

Time(Time#('0538AM','hhmmTT'),'hh:mm') returned 05:38

Time(Time#('0538PM','hhmmTT'),'hh:mm') returned 17:38

converting a data string into time

swuehl — Fri, 29 Jul 2011 20:25:28 GMT

I also double checked.

I get 12:00:00 AM if I use

=time(num#(mid('0514am',1,4)))

or null using

=time(time#(mid('0514am',1,4)))

i.e. if I forget the format code for the string parsing.

=time(time#(mid('0514am',1,4),'hhmm'))

gives a correct time format depending on your system settings.

If you have still troubles, it it possible for you to upload a sample file?

Stefan

converting a data string into time

Mon, 01 Aug 2011 18:52:50 GMT

how do i upload the file to the discussion?

converting a data string into time

swuehl — Mon, 01 Aug 2011 18:58:28 GMT

Hi Matt,

there is an upload function in advanced editor (link upper right corner of editor).

Re: converting a data string into time

Mon, 01 Aug 2011 19:39:34 GMT

Thanks i got it now. Im pretty new to this.

here is a sample file of the data.

Thanks so much.

Matthew

Re: converting a data string into time

swuehl — Mon, 01 Aug 2011 19:49:21 GMT

I've added a listbox with expression to convert string TIME to a real time format.

You should be able to use that also in the script (without your data sources, I couldn't check).

Stefan

converting a data string into time

Mon, 01 Aug 2011 19:54:13 GMT

would you mind posting the code?

I am not working on my copy of QV today but on an unregistered version so I cannot open the file.

That would be much appreciated.

Thanks

Re: converting a data string into time

swuehl — Mon, 01 Aug 2011 20:01:39 GMT

I might have an idea what your problem was:

Have you used Time1 (the resulting number after string parsing) for time functions?

I think this will not work, time is encoded as decimal places after the whole number, e.g 09:00 am 0.375.

In this example, you parsed 900 into Time1 as a number, this results always to 12:00 AM, because there are no decimal places.

Stefan

Re: converting a data string into time

swuehl — Mon, 01 Aug 2011 20:03:51 GMT

Sure, here you are:

=time(time#(TIME,'hhmmtt'))

Re: converting a data string into time

Mon, 01 Aug 2011 20:39:25 GMT

Hi,

So i ended up using a code similar to the one you suggested in addition to the information you gave me about how QV operates with its time function.
time#(time((num#(Left(TIME,4))/2400),'hhmmtt'),'HHMM')

Thanks for all of your help. I think this solves my problem.
Matt

converting a data string into time

Thu, 24 May 2012 15:25:38 GMT

Some cases you have 105 to represent 1:05 AM without 0´s at the begining of the string.

In case this worked for me:

if(Len(TIME)=4 ,Time(Time#(TIME,'hhmm'),'hh:mm') , if(Len(TIME)=3 ,Time(Time#('0'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=2 ,Time(Time#('00'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=1 ,Time(Time#('000'&TIME,'hhmm'),'hh:mm') , 0 ) ) ) )

Hope this help