topic Re: Date ISO issue in QlikView

Date ISO issue

Anonymous — Wed, 04 Feb 2015 11:46:47 GMT

I usually get the month name using MonthName (Date Column) as Month in the script. But this time it is not working since my the date values are in some ISO format. Date vaules in the date column is like as follows. Any suggestions please???

2015-01-05T20:13:58

Re: Date ISO issue

robert_mika — Wed, 04 Feb 2015 11:54:58 GMT

Try:

=monthname(SubField('2015-01-05T20:13:58','T',1))

Re: Date ISO issue

Anonymous — Wed, 04 Feb 2015 12:03:33 GMT

Thanks for replying.

I dont know where to use this.

I have the below fields in my table

Title

Status

Date

Since I dont have a month field. I usually include a field as Month (Date) as Month so that I can use a dimension as Month. This usually works. But this time the date format is different in my file. It is like below. So not working. Please suggest on how to create a filed for month in my script.

My Script:

LOAD ID,

TrackingID,

RequestID,

DateCompleted,

Month (DateCompleted) as Month

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

DateCompleted

2015-01-03T16:55:08

2015-01-03T17:04:40

2015-01-05T16:00:42

2015-01-05T16:01:08

2015-01-05T03:34:46

2015-01-05T16:04:09

2015-01-05T16:14:32

2015-01-05T20:13:58

2015-01-05T20:24:35

2015-01-06T05:14:20

2015-01-06T05:24:27

2015-01-06T07:24:50

2015-01-06T07:26:00

2015-01-06T08:14:03

Re: Date ISO issue

MarcoWedel — Wed, 04 Feb 2015 12:08:28 GMT

LOAD *,

Month (DateCompleted) as Month;

LOAD ID,

TrackingID,

RequestID,

Date(Date#(PurgeChar(DateCompleted,'T'),'YYYY-MM-DDhh:mm:ss')) as DateCompleted

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

Re: Date ISO issue

MarcoWedel — Wed, 04 Feb 2015 12:10:34 GMT

or better

LOAD *,

Month (DateCompleted) as Month;

LOAD ID,

TrackingID,

RequestID,

Date(Date#(Replace(DateCompleted,'T',' '),'YYYY-MM-DD hh:mm:ss')) as DateCompleted

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

if there might be days or hours with only one digit length.

regards

Marco

Re: Date ISO issue

robert_mika — Wed, 04 Feb 2015 12:10:57 GMT

LOAD ID,

TrackingID,

RequestID,

Monthname(SubField(DateCompleted,'T',1))

Month (DateCompleted) as Month

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

Re: Date ISO issue

Anonymous — Wed, 04 Feb 2015 12:28:16 GMT

@ Robert Mika - Excellent it worked.

Thanks Marco

Re: Date ISO issue

robert_mika — Wed, 04 Feb 2015 13:00:43 GMT

You are welcome