topic Issue with different date formats in QlikView

Issue with different date formats

ferha_jafri — Fri, 11 Oct 2013 12:30:24 GMT

Hello Everyone,

I am facing an issue as i have a table in which all the date fields are in different - different formats some are in number,some are in text some contains the date along with time stamp,due to which i am not able to get the correct value and correct selections,Please let me know what is best in this case.

Thanks in advance

With regards

Ferha Jafri

Re: Issue with different date formats

Fri, 11 Oct 2013 12:37:05 GMT

Ferha,

Just apply

Date(date_field) as Date

in your script and you will be good to go.

Re: Issue with different date formats

tresB — Fri, 11 Oct 2013 12:40:24 GMT

During load all of them should be transformed to proper date values using different date functions like:

Date(DateField, 'MM-DD-YYYY') as Date1

If you have text like 11Sep2013, then

Date(Date#(DateField, 'DDMMMYYYY'), 'MM-DD-YYYY') as Date2

Or, you might have to use MakeDate() function some where depending on your data format.

Otherwise, maintaining a master calendar is a good idea, though that depends on your model.

Re: Issue with different date formats

Fri, 11 Oct 2013 12:56:24 GMT

Hello,

If you want to display your data as date, you can make Date(Date) in your script,

you can also make Date()*1 to have all in Number Format (it is sometimes helpfull to store data as number to make calculations and set analysis).

Re: Issue with different date formats

Gysbert_Wassenaar — Fri, 11 Oct 2013 13:35:29 GMT

See this blog post: Get the Dates Right