topic Re: Date-time format lost after loading from QVD in QlikView

Date-time format lost after loading from QVD

dawgfather — Wed, 25 Nov 2020 16:16:04 GMT

I am new to using a QVD file and have run into something interesting. I have a check in place to see if the QVD file exists and if it does not, then run a SQL query to load it and then save it to a QVD file.

My SQL query has a field that is a datetime field from what I can tell and when the SQL loads I get results like:

10/22/2016 8:00:00 PM

10/1/2016 10:00:00 PM

...and all this is great. It is as I want it. But when I next run the reload it loads that same file from the QVD but then my chart details change and I end up with the following:

42665.833333333

42644.916666667

Why does loading it from the QVD change/alter the output?

Re: Date-time format lost after loading from QVD

Anil_Babu_Samineni — Thu, 29 Sep 2016 20:15:24 GMT

Sometimes it will happen

After reload the qvd you can use

Date(date#(datefield,'mm/dd/yyyy hh:mm:ss tt'),'required')

Date(num(datefield),'required format')

Re: Date-time format lost after loading from QVD

marcus_sommer — Fri, 30 Sep 2016 07:55:32 GMT

I think you need to format your timestamp before storing the data into the qvd - maybe with a Preceding Load:

load *, timestamp(datetime, 'MM/DD/YYYY hh:mm:ss TT') as DateTimeFormatted;

SQL select * From MySQL;

- Marcus

Re: Date-time format lost after loading from QVD

dawgfather — Fri, 30 Sep 2016 13:18:19 GMT

Thank you...this worked, but I still do not understand why it works when the data is first pulled but after loading from the QVD instead of SQL, it changes.

Re: Date-time format lost after loading from QVD

Anil_Babu_Samineni — Fri, 30 Sep 2016 16:10:24 GMT

Sometime, Qlikview frustrated at us. Anyway, I got same issue few months ago. I did same thing. Still i don't know what is the problem to find?

Can you confirm me, Who created that qvd? And if answer is yes, then are you sure before store to qvd do you have Date format of your field of date or number field?

If Yes, Share me the data which you have for ref