5 Replies Latest reply: Feb 21, 2012 8:21 AM by Dennis Hoogenboom RSS

    Date (time) format challenge

    Dennis Hoogenboom

      Dear specialist,

       

      I got a format challenge.

      The datefield from my data ocures in different formats.

      Obvoius I need them to be all the same.

       

      Here are some sample of all the formats that are in my data

       

      DateTime format info:
      13:35pmToday
      Yesterday 10:25amYesterday
      18 Feb 2:01pmDates this year
      3 Jan 11:02amDates this year
      31 Nov 2011Dates previous year (and older)

       

      I can not change the format directly in my data because it is loaded from a website, so I need to solve this in the loading script.

       

      Any suggestions?

       

       

      Dennis.

        • Date (time) format challenge
          jagan mohan rao appala

          Hi,

           

          Try using the Alt().  Hope it helps you.

           

          Regards,

          Jagan.

            • Date (time) format challenge
              Dennis Hoogenboom

              Hi Jagan,

               

              Thanks for your respond, but can you give me an example of the load script bases on the Excel file I atteched?

              Becasue it doesn't make sence to me.

               

               

              Thanks,

              Dennis.

                • Re: Date (time) format challenge
                  Dennis Hoogenboom

                  I am almost there with this script:

                   

                  SET ThousandSep='.';

                  SET DecimalSep=',';

                  SET MoneyThousandSep='.';

                  SET MoneyDecimalSep=',';

                  SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

                  SET TimeFormat='h:mm:ss';

                  SET DateFormat='DD-MM-YYYY';

                  SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

                  SET MonthNames='Jan;Feb;Mrt;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                  SET DayNames='ma;di;wo;do;vr;za;zo';

                   

                  Let vCurrentYear = Year(today());

                  Let vToday = Date(today());


                   

                  DateFormat:

                   

                  LOAD

                            Replace

                                      (Replace

                                                (Replace

                                                          (Replace

                                                                    (Replace

                                                                              (Replace

                                                                                        (Replace

                                                                                                  (Replace

                                                                                                            (Replace

                                                                                                                      (Replace

                                                                                                                                (Replace

                                                                                                                                          (Replace

                                                                                        (Datum ,

                                                          ' Jan ' , '-01-'),

                                                          ' Feb ' , '-02-'),

                                                          ' Mar ' , '-03-'),

                                                          ' Apr ' , '-04-'),

                                                          ' May ' , '-05-'),

                                                          ' Jun ' , '-06-'),

                                                          ' Jul ' , '-07-'),

                                                          ' Aug ' , '-08-'),

                                                          ' Sep ' , '-09-'),

                                                          ' Oct ' , '-10-'),

                                                          ' Nov ' , '-11-'),

                                                          ' Dec ' , '-12-')

                   

                                                          as Datum,

                                                          Datum as DateAfterLoad1,

                                                          Date as OrgDateFormat;

                   

                   

                  LOAD

                  IF (Right(Date, 1) <> 'm' , Date ,

                            IF ( LEFT(Date, 1) = 'Y' , ($(vToday) -1),

                                      IF          (Right(Date, 1) = 'm' AND  len(Date) > 10 , (TRIM(LEFT(Date,6))&' '& $(vCurrentYear)),

                                                IF (len(Date) < 10  , ($(vToday))

                                                          )

                                                           )

                                                            )

                                                             )

                                                              as Datum,

                                                              Date

                            FROM

                  Dates.xlsx

                  (ooxml, embedded labels, table is Blad1);

                   

                   

                  Only somehow my Today() variable acts wierd.

                  In an input box it show the correct date but not in a text field.

                   

                  See attachment.

                   


                    • Date (time) format challenge
                      Stefan Wühl

                      I think you should use

                      =Date('$(vDayToday)')

                       

                      for your text box. The other way it is first calculating 21-02-2012 (21 minus 2 minus 2012) and then trying to parse that in (you used date#() function) or format (using date() function). The single quotes prevent Qv from doing this.

                       

                      And for your load, you could try using the mentioned alt() function like that:

                       

                      Dates:

                      LOAD *,

                      timestamp(

                      alt(

                      if(time#(Date,'hh:mmTT'),time#(Date,'hh:mmTT')+today(1)),

                      if(wildmatch(Date,'Yesterday*'),time#(right(Date,7),'hh:mmtt')+today(1)-1),

                      if(date#(Date,'d MMM hh:mmtt'),date#(Date&year(today(1)),'d MMM hh:mmttYYYY')),

                      date#(Date,'d MMM YYYY')

                      )

                      ) as NewDateField;

                      LOAD * INLINE [

                      Date,        Time format info:

                      01:35pm,        Today

                      Yesterday 10:25am,        Yesterday

                      18 Feb 2:01pm,        Dates this year

                      3 Jan 11:02am,        Dates this year

                      30 Nov 2011,        Dates previous year (and older)

                      ];

                       

                      Hope this helps,

                      Stefan