Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

number to time converstion

I am uploading data from txt file and while file saved in txt file hrs, minutes and seconds get converted to a number.  how do I convert back to time in QV?

16 Replies
swuehl
Not applicable

Re: number to time converstion

Could you give an example of the format of your text file and how the interpreted number looks like?

QV sometimes interpretes strings as date and time automatically, a time value is then stored as fraction of day, e.g 18:00:00 in the evening would translate to number 0.75

If you think that what has happened, you can use a format function to format your number given a format code, like

=Time( 0.75, 'hh:mm:ss')

or

=Time(0.75, 'hh:mm:ss tt')

Have a look at the HELP, search for 'format codes' and 'date and time functions'.

Not applicable

Re: number to time converstion

The top is original source file that I save as txt (below) and load to QV.  4:40 ACD time got converted to 280.060211182.   How do I convert back to 4:40 in QV?

Question1.JPG

jolivares
Not applicable

Re: number to time converstion

If think that you have a time converted to minute+fraccion of minutes.  In this case

280.060211182/60=4.666667

0.66666667*60=40



jolivares
Not applicable

Re: number to time converstion

Could be: =MakeTime(Floor(280.060211182/60),Floor(Frac(280.060211182/60)*60))

Not applicable

Re: number to time converstion

ok - that solve minutes / seconds but next one is hrs / minutes / seconds...  how would you approach that?

jolivares
Not applicable

Re: number to time converstion

MakeTime(Floor(280.060211182/60),Floor(Frac(280.060211182/60)*60),Floor(Frac(Frac(280.060211182/60)*60)*60))

Not applicable

Re: number to time converstion

let me play with it to see if I can get it to work.

Not applicable

Re: number to time converstion

how are you reading in the file?  I could see a format changing in Excel but not from a txt file

any chnace you can send a subset of the file?

Not applicable

Re: number to time converstion

see attached