topic Re: Loading time date from Excel changes time values slightly in QlikView

Loading time date from Excel changes time values slightly

Wed, 25 Nov 2020 16:16:04 GMT

See attached QVW with the Excel data am trying to load. You will notice that in the Excel sheet the date time is 4/1/2016 0:30 but when it loads the file in QV, it's showing as 4/1/2016 12:29:59 AM. It should be showing 12:30:00 AM. Why is it behaving like this? Anything special I need to do when loading the data?

Hopefully someone can help.

Thanks,
Amit

Re: Loading time date from Excel changes time values slightly

MindaugasBacius — Mon, 30 May 2016 11:51:34 GMT

QV shows the same information as the data file contains.

Check the data in the file:

Re: Loading time date from Excel changes time values slightly

Mon, 30 May 2016 11:58:06 GMT

Did you check at the specific example I pointed out in the original post? This is showing different from the file and in QV - see picture below.

Thanks,

Amit

Re: Loading time date from Excel changes time values slightly

MindaugasBacius — Mon, 30 May 2016 12:16:34 GMT

My mistake.

The difference appear in the QV because it rounds the last numbers:

Re: Loading time date from Excel changes time values slightly

Mon, 30 May 2016 12:19:07 GMT

Thanks, do you have any recommendation on how to correct this?

Re: Loading time date from Excel changes time values slightly

marcus_sommer — Mon, 30 May 2016 12:45:38 GMT

The reasons are explained here: Rounding Errors. To catch these differences you could round these values to a second, like:

timestamp(floor([Date Interval], 1/24/60/60))

And if you are handling with bigger datasets you should consider to split the timestamp into a date and a time.

- Marcus

Re: Loading time date from Excel changes time values slightly

Kushal_Chawda — Mon, 30 May 2016 12:54:44 GMT

slight modification for whatmarcus_sommer‌ suggested

Use round instead of Floor

Data:

LOAD

timestamp(Round([Date Interval], 1/24/60/60)) as Date_Interval,

[Date Interval],

Forecast,

AHT,

FTE,

E_Forecast,

E_AHT,

E_FTE

FROM

(biff, embedded labels, table is Forecast$);

Re: Loading time date from Excel changes time values slightly

Mon, 30 May 2016 13:10:52 GMT

Thanks guys! I was able to make it work. I chose separating the date and time.

Re: Loading time date from Excel changes time values slightly

Kushal_Chawda — Mon, 30 May 2016 13:16:15 GMT

Storing the timestamp field in QlikView is not advisable as it occupied more memory, so split the Timestamp into Date & Time field separately,

Symbol Tables and Bit-Stuffed Pointers

Comment the Timestamp field

Data:

LOAD

Time(round(Frac([Date Interval]),1/24/60),'hh:mm:ss TT') as Time,

date(Floor([Date Interval])) as Date,

[Date Interval],

Forecast,

AHT,

FTE,

E_Forecast,

E_AHT,

E_FTE

FROM

(biff, embedded labels, table is Forecast$);