5 Replies Latest reply: Jul 18, 2016 3:38 AM by Marcus Sommer RSS

    Source Date Field Problem

    Michael Taylor

      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

          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
              Michael Taylor

              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

              [C:\Users\mike\Desktop\testbook.xlsx]

              (ooxml, embedded labels)

                • Re: Source Date Field Problem
                  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

                    • Re: Source Date Field Problem
                      Michael Taylor

                      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

                          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