topic Re: Irreguar Date format in QlikView

Irregular Date format

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

Hi All

I have date format like shown below. How should I read this in QV.

Date:

14/5/2016

04/05/16

29/4/2016

01/05/16

30/4/2016

31/5/2016

29/4/2016

27/4/2016

03/05/16

30/4/2016

Re: Irreguar Date format

Kushal_Chawda — Wed, 18 May 2016 07:05:56 GMT

try using alt

=date(alt( Date#(DateField,'DD/MM/YYYY'),Date#(DateField,'DD/MM/YY')),'DD/MM/YYYY')

Re: Irregular Date format

Chanty4u — Wed, 18 May 2016 07:07:09 GMT

chk dis

Re: Convertion of diff date format in to unique format

Re: Need help to make the date into single format in Qlikview

Re: Irregular Date format

ankit777 — Wed, 18 May 2016 08:22:30 GMT

It did not work

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 08:29:15 GMT

I believe that this alone seems to be working:

SET DateFormat='DD/MM/YYYY';

Table:

LOAD Date(@1) as Date

FROM

[https://community.qlik.com/thread/217289]

(html, codepage is 1252, no labels, table is @1);

Re: Irregular Date format

Kushal_Chawda — Wed, 18 May 2016 08:37:24 GMT

what is the format of you Date? As per your snapshot, I can see date format 'DD/MM/YYYY' and 'DD/MM/YY', so below should work

LOAD Date,

date(alt( Date#(DateField,'DD/MM/YYYY'),Date#(DateField,'DD/MM/YY')),'DD/MM/YYYY') as Date

....

FROM Source

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 08:42:06 GMT

Kush -

Have you looked at the sample I posted? We might not even need to use Alt() function here. Look at this discussion we had yesterday:Re: date format - includes a . and read marcowedel‌'s response

Re: Irregular Date format

Kushal_Chawda — Wed, 18 May 2016 09:13:34 GMT

What if the data is coming as string format from source(QVD or Excel)?

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 09:16:02 GMT

I have not tested this, but if you specify this -> SET DateFormat='DD/MM/YYYY'; at the top, QlikView should be able to read string as date as well (assuming that the string is either DD/MM/YYYY or DD/MM/YY)

Re: Irregular Date format

ankit777 — Wed, 18 May 2016 09:17:23 GMT

issue was same. I was getting string instead of dates. Thanks for help

Re: Irregular Date format

Kushal_Chawda — Wed, 18 May 2016 09:19:04 GMT

I think if Date coming as string then we need to use Date# only.. SET will not be able to recognize the format

Re: Irregular Date format

Chanty4u — Wed, 18 May 2016 09:24:05 GMT

CHK THIS

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 09:24:19 GMT

I am going to test that out right now

Re: Irregular Date format

Chanty4u — Wed, 18 May 2016 09:25:12 GMT

yes kush set is not working

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 09:30:29 GMT

Check out the attached:

SET DateFormat='DD/MM/YYYY';

Table:

LOAD Date(A) as Date

FROM

Community_217289.xlsx

(ooxml, no labels, table is Sheet1);

Excel

Re: Irregular Date format

Chanty4u — Wed, 18 May 2016 09:31:22 GMT

i dnt know why it is not workng for me

Re: Irregular Date format

Kushal_Chawda — Wed, 18 May 2016 09:34:34 GMT

Looks ok, but I still have doubt, otherwise Date# function would no longer be available in QlikView to interpret string values as Date

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 09:40:21 GMT

Well it would be still needed when formats are like this

25/01/2016

01/25/2016

This cannot be handled without Alt()

but it seems this can:

25/01/2016

25/01/16

Re: Irregular Date format

Kushal_Chawda — Wed, 18 May 2016 09:43:34 GMT

Need to test on other sources as well.

Re: Irregular Date format

sunny_talwar — Wed, 18 May 2016 09:44:15 GMT

Other source?