topic Re: Date Format Issue in QlikView

Date Format Issue

Fri, 21 Nov 2014 07:56:31 GMT

Hi,

I have a date field which has values in two formats. The values prior to 2014 are in the format 'MMM/DD/YYYY' whereas in 2014 the format 'DD-MMM-YYYY'.

I want to derive Year value out of this date field. How do i do that?

Thanks,

Asma

Re: Date Format Issue

alexandros17 — Fri, 21 Nov 2014 07:59:27 GMT

if both formats are recognised as date field simply use year(myField)

let me know

Re: Date Format Issue

Fri, 21 Nov 2014 08:04:38 GMT

Both of them are not recognised as date fields. For the dates in the format 'DD-MMM-YYYY', (i.e. only 2014 dates), we are getting correct values for year(myField), but for the dates in the format 'MMM/DD/YYYY', year(myField) returns null

Re: Date Format Issue

alexandros17 — Fri, 21 Nov 2014 08:10:27 GMT

try to format dates, with date#(myField,'DD-MMM-YYYY')

Re: Date Format Issue

jagan — Fri, 21 Nov 2014 08:14:35 GMT

Hi,

Try like this using Alt()

LOAD

Date(Alt(Date#(DateFieldName, 'MMM/DD/YYYY'), Date#(DateFieldName, 'DD-MMM-YYYY'))) AS NewDateField

FROM DataSource;

Note: Replace DateFieldName with your actual field name.

Hope this helps you.

Regards,

Jagan.

Re: Date Format Issue

Anonymous — Fri, 21 Nov 2014 08:14:56 GMT

Hey,

User year(Date(myFiled)) should recognize.

BR,

chinna

Re: Date Format Issue

MK_QSL — Fri, 21 Nov 2014 08:33:52 GMT

Load

DateField,

Month(DateField) as Month,

Year(DateField) as Year;

Load

Date(Alt(Date#(DateField,'MMM/DD/YYYY'), Date#(DateField,'DD-MMM-YYYY'))) as DateField

Inline

[

DateField

Jan/01/2012

Mar/05/2013

25-Nov-2014

];

Re: Date Format Issue

Fri, 21 Nov 2014 09:41:56 GMT

Thanks Jagan...

But after using the alt() function, the NewDateField which is created only contains those values which had format as MMM/DD/YYYY. They are converted into DD-MMM-YYYY format which is correct. But those dates which were already in the format DD-MMM-YYYY do not appear in the NewDateField.

Re: Date Format Issue

Anonymous — Fri, 21 Nov 2014 10:04:55 GMT

Try this

=year(IF(ISTEXT([datefieldname])=0,Date(NUM([datefieldname])),date(date#([datefieldname],'MMM/DD/YYYY'),'MMM/DD/YYYY')))

Thanks,

Nisha loganathan

Re: Date Format Issue

Fri, 21 Nov 2014 12:49:40 GMT

Thanks a lot Nisha!!!