topic Re: Date handling in QlikView

Date handling

Anonymous — Thu, 18 Jan 2018 17:47:23 GMT

I have a column in an Excel sheet that contains dates and partial dates, i.e. format. dd/mm/yyyy, mm/yyyy and yyyy. I would like to extract the month and year or null where there is none. Using the Year and Month functions, Qlik extracts 1905 and May, when there isn't one. Any ideas?

Re: Date handling

Anil_Babu_Samineni — Fri, 19 Jan 2018 18:37:45 GMT

Can you provide sample data?

Re: Date handling

Anonymous — Fri, 19 Jan 2018 20:22:25 GMT

It seems QV is not reading the dates properly. Sample data would be great.

Re: Date handling

m_woolf — Fri, 19 Jan 2018 20:28:43 GMT

For year:

=if(len(DateField=10,year(DateField),

if(len(DateField)=7,subfield(DateField,"/",2),DateField) as YearField

For month:

=if(len(DateField=10,month(DateField),

if(len(DateField)=7,subfield(DateField,"/",1),null()) as MonthField