Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date from QVD not exactly the same as Source

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:MMSmiley FrustratedS 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

Tags (3)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Date from QVD not exactly the same as Source

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

11 Replies
rajeshvaswani77
Valued Contributor III

Re: Date from QVD not exactly the same as Source

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

Not applicable

Re: Date from QVD not exactly the same as Source

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)

rajeshvaswani77
Valued Contributor III

Re: Date from QVD not exactly the same as Source

Hi Janet,

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

Hope it will be helpful.

thanks,

Rajesh Vaswani

Not applicable

Re: Date from QVD not exactly the same as Source

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?

rajeshvaswani77
Valued Contributor III

Re: Date from QVD not exactly the same as Source

Hi Janet,

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

thanks,

Rajesh Vaswani

Highlighted
MVP
MVP

Re: Date from QVD not exactly the same as Source

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

Not applicable

Re: Date from QVD not exactly the same as Source

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?

MVP
MVP

Re: Date from QVD not exactly the same as Source

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.

Not applicable

Re: Date from QVD not exactly the same as Source

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.

Community Browser