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: 
Not applicable

converting a data string into time

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

13 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

nagaiank
Specialist III
Specialist III

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

swuehl
MVP
MVP

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

Not applicable
Author

how do i upload the file to the discussion?

swuehl
MVP
MVP

Hi Matt,

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

Not applicable
Author

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

here is a sample file of the data.

Thanks so much.

Matthew

swuehl
MVP
MVP

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

Not applicable
Author

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