Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

QlikView Date fields

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

QlikView Date fields

Last Update:

May 31, 2012 3:29:49 AM

Updated By:

hic

Created date:

May 31, 2012 3:29:49 AM

Attachments

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

HIC

Comments
Not applicable

As always - useful stuff!

Thanks Henric.

0 Likes
Not applicable

I am looking for a way to change the date format. the format in the data load is d/m/yyyy. is there a way I can change it in qlikview to show as mm/d/yyyy? I have tried a few things, but nothing seems to work.

0 Likes
hic
Former Employee
Former Employee

To format the dates, you should use

     Date( FieldName, 'MM/D/YYYY')

Note the upper case in the format code! Lower case m means minutes, so that will not work.

Perhaps you need an interpretation function first, though:

     Date( Date#( FieldName, 'D/M/YYYY'), 'MM/D/YYYY')

Read more here:

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

HIC

Not applicable

Thank you very much for this.

For novices uploading spreadsheets  the MakeDate function is amazing, gets me the data I needed into the file without having to irritate the source team with lots of hidden Excel columns etc.

Thank you Henric, you have saved me a lot of work and I am very grateful!

0 Likes
rik_qlik
Contributor III
Contributor III

Very useful.

Thanks Henric.

0 Likes
Not applicable

It was a very precise and useful information!!! Thank You very much. Cleared most of my confusions about when to use # functions and what's the purpose of both!

0 Likes
ecolomer
Master II
Master II

Buen trabajo.

Excelente documento

Saludos,

Enrique Colomer

0 Likes
taha_mansoor
Creator
Creator

hic

Hi,

Excellent post, I must say.

Just one question pop-up in my mind. In the document above, in the opening para, it is told that Qlikview stores data for all field values in Dual format by default i.e. text and number. On page 3, second para, it is told that the Date() function store the date serial into string as well as in number, which means its stores in dual.


On the other hand, in summary, it has been advised that anything like date or time should always be converted into date serial number and use that in dual as number part and text part is of personal choice.


So the summary words seem like a best practice advised by the author. But analysing the above, I have a question that when Qlikview is automatically storing all data values (which have been interpreted successfully and correctly) in dual format (if applicable) then why shall we need to use dual function explicitly for the same purpose when its already done by Qlikview ?  Is there any other concern for using Dual ?

Thanks

Taha

0 Likes
hic
Former Employee
Former Employee

If the Qlik engine automatically has interpreted a date field, or if you use the Date#() function to create a dual field, then you should not use the Dual() function.

But in some cases, e.g. if you want to create a week number like '2018-w09', then you need to use the Dual() function instead. So, it is one or the other, but not both.

HIC

612118513
Contributor II
Contributor II

Great document, explain clearly how qlikview interpretate the date fields, some problems and how to solve them.

 

Great Job

0 Likes
Version history
Last update:
‎2012-05-31 03:29 AM
Updated by:
Former Employee