topic Convert String to Date in QlikView

Convert String to Date

Anonymous — Mon, 28 May 2012 08:03:09 GMT

Hi,

I have the ffg data in an excel sheet:

May 1 2012 12:00AM

May 2 2012 12:00AM

May 3 2012 12:00AM

May 4 2012 12:00AM

May 7 2012 12:00AM

May 8 2012 12:00AM

May 9 2012 12:00AM

May 10 2012 12:00AM

May 11 2012 12:00AM

May 12 2012 12:00AM

When I Load this data into Qlikview I use the ffg LOAD statement:

LOAD

Timestamp#(XACT_DAT, 'MMM dd yyyy hh:mmtt') AS XACT_DAT

FROM

[May.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The problem is that the only values that are converted to a Date are:

May 10 2012 12:00AM May 11 2012 12:00AM May 12 2012 12:00AM

All the Days with single digits are not converted to a Date,

Could someone kindly assist me with this problem,

Kind Regards

Razak

Convert String to Date

Mon, 28 May 2012 08:46:25 GMT

Hi Razak,

I think if you use

LOAD

Timestamp#(XACT_DAT, 'MMM dd yyyy hh:mmtt') AS XACT_DAT

FROM

[May.csv]

(ooxml, codepage is 1252, embedded labels, delimiter is ',', msq);

This will work.

Re: Convert String to Date

Miguel_Angel_Baeyens — Mon, 28 May 2012 08:56:30 GMT

Hi,

If you mean there are some more blanks between the month name and the day number, you may need to remove these so the format pattern applies to all of them:

Timestamp#(Replace(XACT_DAT, '  ', ' '), 'MMM dd yyyy hh:mmtt') AS XACT_DAT

Hope that helps.

Miguel