topic Re: number to time converstion in QlikView

number to time converstion

Wed, 30 Sep 2015 13:08:37 GMT

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?

Re: number to time converstion

swuehl — Wed, 30 Sep 2015 17:44:35 GMT

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')

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

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

Re: number to time converstion

Wed, 30 Sep 2015 17:54:55 GMT

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?

Re: number to time converstion

jolivares — Wed, 30 Sep 2015 19:01:31 GMT

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

Re: number to time converstion

jolivares — Wed, 30 Sep 2015 19:04:30 GMT

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

Re: number to time converstion

Wed, 30 Sep 2015 19:13:29 GMT

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

Re: number to time converstion

jolivares — Wed, 30 Sep 2015 19:19:02 GMT

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

Re: number to time converstion

Wed, 30 Sep 2015 19:20:53 GMT

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

Re: number to time converstion

Wed, 30 Sep 2015 19:27:21 GMT

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?

Re: number to time converstion

Wed, 30 Sep 2015 19:32:07 GMT

see attached

Re: number to time converstion

Wed, 30 Sep 2015 19:34:50 GMT

can you send a subset of the file you are reading in? The attached already has the time converted

I would like to see if I can see where in the process the time is getting converted so I need a source of data that has the field in the time format

Re: number to time converstion

Wed, 30 Sep 2015 19:37:49 GMT

I dont have unconverted file. the system allows me to save txt output format and thats when conversion takes place. the unconverted format is just screenshot of the software.

Re: number to time converstion

jolivares — Wed, 30 Sep 2015 19:42:24 GMT

My friend, you already have this file loaded in QV, just apply the formula that i give you before and get the results done.

Re: number to time converstion

Wed, 30 Sep 2015 19:44:54 GMT

can you send a screen shot when you are loading the unconvered file - the screen from the file wizard that shows how the system is reading the data

If you skip the transform set in the wizard, the data will not get converted

I modified the file you sent me, put the time in and when I checked the Enable transformation step button, it was converted from time to a number but when I did not do that step and just clikced next, the time was not converted to a number

Re: number to time converstion

Wed, 30 Sep 2015 19:48:09 GMT

my source file (txt) has already converted. thats what i load to QV. in QV i have to make it back to original view - unconverted.

Re: number to time converstion

Wed, 30 Sep 2015 19:48:59 GMT

I will try shortly and let you know.

Re: number to time converstion

Wed, 30 Sep 2015 19:54:34 GMT

ahhh - I did not realize that because I can see QlikView converting it as well when certain steps are followed.

My understanding was incorrect - thought you were reading the dta from the initial source ,.txt file, did not realize you were getting a converted file