topic Date issues in QlikView

Date issues

_armoco_ — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

So I have this excel with dates. Half of the dates are in MM-DD-YYYY format and the other half in MM/DD/YYYY.

Now here is the thing. I load this thing into QlikView and I get this:

Note that the second half of the data is now suddenly in DD-MM-YYYY instead of the original format MM/DD/YYYY.

Been playing around with date functions for sometime now and nothing seems to work.

How do I fix this and bring all the dates into a single format? I don't mind if its in DD-MM-YYYY or MM-DD-YYYY or anything, but I just need it in the same format.

Attaching the file.

Thanks,

Arjun

Re: Date issues

devarasu07 — Mon, 15 Jan 2018 06:59:59 GMT

Hi,

Try like this format in your script

Date(Floor(Date),'D/M/YYYY') as DateNew;

Re: Date issues

_armoco_ — Mon, 15 Jan 2018 07:03:54 GMT

Hi Devarasu,

Thank you for your reply. Tried it out:

Dates upto 12th are missing now

Thanks,

Arjun

Re: Date issues

tresB — Mon, 15 Jan 2018 07:05:03 GMT

Are you sure the dates in red highlighted are in MM-DD-YYYY format? The DD-MM part in screen shows no value bigger than 12, so it could be DD-MM-YYYY also.

Re: Date issues

Anil_Babu_Samineni — Mon, 15 Jan 2018 07:07:51 GMT

May be play around using alt - script and chart function ‒ QlikView

Re: Date issues

_armoco_ — Mon, 15 Jan 2018 07:16:05 GMT

Hi Tresesco,

I'm sure its MM-DD-YYYY. Its my clients data from 1st to 21st December

Thanks,
Arjun

Re: Date issues

_armoco_ — Mon, 15 Jan 2018 07:19:54 GMT

Hi,

Tried Date(Floor(Date),'D/M/YYYY') as DateNew;

Dates are still messed up

Thanks and Regards,

Arjun

Re: Date issues

jonathandienst — Mon, 15 Jan 2018 07:36:05 GMT

You have two date formats in your input. This will get all the dates:

...

Date(Alt(Date#(Date, 'yyyyMMdd'), Date#(Date, 'MMddyyyy'), 'ddMMyyyy') as Date,

...

Adjust the last format string to the format you require if you don't want DMY.

Re: Date issues

tresB — Mon, 15 Jan 2018 07:40:33 GMT

Your source data is not so. If you load the as text you would see those dates have numbers which are actually not of Dec months. Hence the correction would be required at the source. Because, if you have the date value (the number) wrong itself at the backend, no formatting, nor parsing can change the date.

Re: Date issues

_armoco_ — Mon, 15 Jan 2018 07:55:48 GMT

Thank you! I guess that will solve it.

Re: Date issues

PrashantSangle — Mon, 15 Jan 2018 07:57:05 GMT

Hi,

This is difficult one. You have to correct in source data. You can't solve it in qlik view script.

Reason behind it is difficult to say

12-02-2017 is 12 Feb 2017 or 2nd Dec 2017 similar for

12-01-2017 is 12 Jan 2017 or 1st Dec 2017 and so on.

Regards,

Prashant Sangle