Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
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.
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