QlikView Date fields

[hic]

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

[#2]

As always - useful stuff!

Thanks Henric.

[#3]

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.

[hic]

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

[#5]

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!

[rik_qlik]

Very useful.

Thanks Henric.

[#7]

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!

[ecolomer]

Buen trabajo.

Excelente documento

Saludos,

Enrique Colomer

[taha_mansoor]

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

[hic]

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]

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

Great Job