Source Date Field Problem

[spikenaylor1]

My source data is coming in with a date field issue.

Date Created

04/02/2014  11:50:00          - Picked up as a correct date field, can filter via year, month etc.

03/26/2014 04:50 PM          - Not recognised as a date format and therefore will be counted overall, but not when filtered down to years/months.

02/26/2014 09:38 AM          - Not recognised as a date format and therefore will be counted overall, but not when filtered down to years/months.

How can i script to alter the source data into a usable date format.

Any help and advice greatly received.

Regards

Spikenaylor

[marcus_sommer]

Try the following:

alt(timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm.ss'), MM/DD/YYYY hh:mm.ss),

    timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), MM/DD/YYYY hh:mm.ss))

- Marcus

[spikenaylor1]

Just cannot get it work

been through it loads

keep getting this error

Error in expression:

')' expected

LOAD [Date Created],

     Value,

alt(timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm.ss'), MM/DD/YYYY hh:mm.ss),

    timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), MM/DD/YYYY hh:mm.ss))

FROM

(ooxml, embedded labels)

[marcus_sommer]

The second formatting-statement in my suggestion wasn't wrapped in single-quotes, therefore try this:

alt(timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm.ss'), 'MM/DD/YYYY hh:mm.ss'),

    timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), 'MM/DD/YYYY hh:mm.ss'))

- Marcus

[spikenaylor1]

Many thanks

didn't quite work.

eventually i figured out that the 04/02/2014  11:50:00 is already recognised as a date and in the UK Date format, so changed the formula to.

alt(timestamp([Date Created], 'DD MMM YYYY'), timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), 'DD MMM YYYY')) as datecreated,

to suit my purposes.

All works fine now, but now trying to reference this field so I don't have to use the formula throughout, so my code should look like later on:

Month(datecreated) as MonthCreated

I realise that I may need to pre load the staement and then reference it something like

Load alt(timestamp([Date Created], 'DD MMM YYYY'), timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), 'DD MMM YYYY')) as datecreated;

Load

Month(datecreated) as MonthCreated,

YearName(datecreated as YearCreated

From blahblahblah;

But I cannot get it to work,

keeps erroring with cannot find datecreated field

any ideas how I can reference this new field later throughout the script.

[marcus_sommer]

You need to change the load-order to:

Load *,

Month(datecreated) as MonthCreated,

YearName(datecreated as YearCreated;

Load alt(timestamp([Date Created], 'DD MMM YYYY'),

                timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), 'DD MMM YYYY')) as datecreated

From blahblahblah;

to get a valid Preceding Load.

Whereby I suggest to consider to associate your timestamp-field to a master-calendar and if the time-part is really needed to a timetable. You could achieve this by splitting your timestamp into a date- and time-field per:

Load *,

date(floor(datecreated)) as Date,

time(frac(datecreated)) as Time;

Load alt(timestamp([Date Created], 'DD MMM YYYY'),

                timestamp(timestamp#([Date Created], 'MM/DD/YYYY hh:mm tt'), 'DD MMM YYYY')) as datecreated

From blahblahblah;

and the HowTo for the master-tables could you find here: How to use - Master-Calendar and Date-Values.

- Marcus