topic Re: Dates issue in QlikView

Dates issue

alec1982 — Wed, 18 Jun 2014 15:06:51 GMT

Hello,

On a date columns I am getting values as 01/01/2010 and July/10/2011 and 10/July/2012

Meaning some of the values are text and some are numbers.. what would be the best way to format the date in this case?

Thxs.

Re: Dates issue

Anonymous — Wed, 18 Jun 2014 15:09:56 GMT

Try Date(Field,'dd-mm-yyyy') as Date

Let me know if this works

Re: Dates issue

Wed, 18 Jun 2014 15:10:01 GMT

Alec

I recommand you use QV help and search in String and Dates functions like

left, mid, right,

makedate, month, year...

check applymap as well to transform jan as 1, feb as 2...july as 7 and so on

best regards

Chris

Re: Dates issue

Clever_Anjos — Wed, 18 Jun 2014 15:13:29 GMT

maybe you can use an "alt" chain to try to convert your dates

alt(

date#(yourfield,'DD/MMM/YYYY'),

date#(yourfield,'MMM/DD/YYYY'),

date#(yourfield,'DD/DD/YYYY')

)

Re: Dates issue

Anonymous — Wed, 18 Jun 2014 15:34:41 GMT

How about this:

//   your original example data
data:
load * inline [
date
01/01/2010
July/10/2011
10/July/2012];

//   map out the month names to numbers
mapmonth:
mapping load * inline [
month, num
January, 1
February, 2
March, 3
April, 4
May, 5
June, 6
July, 7
August, 8
September, 9
October, 10
November, 11
December, 12];

//   format the date for the three scenarios given.
data2:
load date,
if(isnum(date),date(date),
if(isnum(subfield(date,'/',1)),makedate(right(date,4),applymap('mapmonth',subfield(date,'/',2)),subfield(date,'/',1)),
makedate(right(date,4),applymap('mapmonth',subfield(date,'/',1)),subfield(date,'/',2)))) as dateformatted,
applymap('mapmonth',subfield(date,'/',2))
resident data;

Re: Dates issue

MK_QSL — Wed, 18 Jun 2014 15:40:58 GMT

Try below in your script...

DateTable:

Load

DateField,

DATE(ALT(Date#(DateField,'DD/MM/YYYY'),

Date#(DateField,'MMMM/DD/YYYY'),

Date#(DateField,'DD/MMMM/YYYY'))) as DateFieldChanged

Inline

[

DateField

01/01/2010

July/10/2011

20/July/2011

];

Re: Dates issue

Anonymous — Wed, 18 Jun 2014 15:43:05 GMT

That is quite elegant, Manish!

Re: Dates issue

alec1982 — Wed, 18 Jun 2014 15:57:14 GMT

Thank you!