Loading time date from Excel changes time values slightly

[#1]

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

[MindaugasBacius]

QV shows the same information as the data file contains.

Check the data in the file:

[#3]

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

[MindaugasBacius]

My mistake.

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

[#5]

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

[marcus_sommer · Accepted solution]

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

[Kushal_Chawda]

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$);

[#8]

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

[Kushal_Chawda]

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$);