topic Re: Date from QVD not exactly the same as Source in QlikView

Date from QVD not exactly the same as Source

Fri, 04 Oct 2013 23:51:35 GMT

Hi,

I created a QVD from Oracle and it has a date field. The Original date showed 7/10/2013 10:20:11 AM.

It saved in QVD as 41465.43068287

So i converted it on the load using Date(CreatedDate,'MM/DD/YYYY HH:MM:SS TT') and showed 7/10/2013 10:07:11 AM

The retrieved date is missing 13 minutes.

Am I doing something wrong?

Thanks for the help

Re: Date from QVD not exactly the same as Source

rajeshvaswani77 — Sat, 05 Oct 2013 02:16:46 GMT

Hi,

Is there are time difference between your QlikView server that generates QVD's as well as the client you are using?

thanks,

Rajesh Vaswani

Re: Date from QVD not exactly the same as Source

Sat, 05 Oct 2013 02:20:22 GMT

No. I am using a personal edition. so all the files are in my local.

And i notice that on the initial load from QVD without date formatting, it will come in correctly. Then i update some of the attributes of other fields, and the re-load, then the date field now shows the numerical value. So then i go back and add the Date() formatting. So I am a bit confused.

I was trying to build logic for incremental load so if the date retrieved was incorrect then my basis(if i included time element ) could be wrong

(disclaimer : I am new to QV)

Re: Date from QVD not exactly the same as Source

rajeshvaswani77 — Sat, 05 Oct 2013 02:29:27 GMT

Hi Janet,

Could you use the attachment for understanding concept of incremental load.

Hope it will be helpful.

thanks,

Rajesh Vaswani

Re: Date from QVD not exactly the same as Source

Sat, 05 Oct 2013 03:34:29 GMT

thank you for the attachment.I am doing page #9.

Also,i just want to know if there is really an issue for QV not converting dates exactly. Since QV converts date fields into number format, is it due to rounding? Or is it my mistake?

Re: Date from QVD not exactly the same as Source

rajeshvaswani77 — Sun, 06 Oct 2013 15:57:41 GMT

Hi Janet,

Behind the date issue you are facing. Whats the lines of code? Could you please post?

thanks,

Rajesh Vaswani

Re: Date from QVD not exactly the same as Source

rbecher — Sun, 06 Oct 2013 17:01:01 GMT

Hi Janet,

you have to use the proper format codes:

=Date(41465.43068287,'MM/DD/YYYY hh:mm:ss TT')

respective:

=Date(CreatedDate,'MM/DD/YYYY hh:mm:ss TT')

Capital 'MM' means the month.. 😉

- Ralf

Re: Date from QVD not exactly the same as Source

Sun, 06 Oct 2013 17:13:44 GMT

thank you. this resolved my problem. I did not know this.

i am wondering how QV determines how to display the date, sometimes it will display the date as 41465.43068287 and sometimes it will display it as it was originally stored. Is there any guidelines on this?

Re: Date from QVD not exactly the same as Source

rbecher — Sun, 06 Oct 2013 17:17:46 GMT

Date/Timestamp is usually stored as dual data type containing a number value and a text representation value (date formatted value).

Sometimes it can happen that only a number is stored. Then you can format it with Date() function or just format the number in chart as Date.

Re: Date from QVD not exactly the same as Source

Sun, 06 Oct 2013 18:12:34 GMT

Hi Ralf,

Is that recommended best practice for retrieving dates from QVD to always use a date() function to ensure proper date formatting and retrieval?

new to QV so i have been trying to read a lot of best practice data modeling and charting for QV.

Re: Date from QVD not exactly the same as Source

rbecher — Sun, 06 Oct 2013 18:18:24 GMT

It is not needed if you format the date fields in the right way before you STORE the QVD.

So, if you have Oracle as source then add a preceding LOAD with Date() or Timestamp() function:

MyTable:

LOAD Date(MyField, ''MM/DD/YYYY hh:mm:ss TT') as MyDate, ... ;

SQL SELECT MyField, ... FROM Table;

- Ralf

Re: Date from QVD not exactly the same as Source

Sun, 06 Oct 2013 18:35:42 GMT

Got it. Thanks Ralf