topic Re: Source Date Field Problem in QlikView

Source Date Field Problem

spikenaylor1 — Thu, 14 Jul 2016 12:22:07 GMT

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

Re: Source Date Field Problem

marcus_sommer — Thu, 14 Jul 2016 12:44:30 GMT

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

Re: Source Date Field Problem

spikenaylor1 — Sat, 16 Jul 2016 07:23:03 GMT

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)

Re: Source Date Field Problem

marcus_sommer — Sun, 17 Jul 2016 10:26:58 GMT

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

Re: Source Date Field Problem

spikenaylor1 — Mon, 18 Jul 2016 07:01:30 GMT

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.

Re: Source Date Field Problem

marcus_sommer — Mon, 18 Jul 2016 07:38:32 GMT

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