3 Replies Latest reply: Nov 16, 2012 8:42 AM by Stefan Wühl RSS

    Date Problem

      Hi,

       

      I have a date column which is from excell file where date is not in proper order like

       

      DateCol:

      APR

      MAY

      1997

      36319

      36901

      18/11/2000

      36893

      14/10/2002

      38821

      39850

      20/09/2009

      40486

      40278

      2003

      2001

      2004

      2005

      2006

      2009

      2010

       

       

      How to create it in a proper order.

      Please help for this it is urgent

       

      Thanks

      Ritul

        • Re: Date Problem
          Stefan Wühl

          You should be able to create a solution using alt() function with some possible date interpretation functions.

          You'll need to decide how to handle year numbers and months names, what date do you want to assign to these?

           

          A solution could look like this:

           

          Set DateFormat = 'DD/MM/YYYY';

          SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

           

          LOAD *, alt(if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol)))) as NewDate INLINE [

          DateCol

          APR

          MAY

          1997

          36319

          36901

          18/11/2000

          36893

          14/10/2002

          38821

          39850

          20/09/2009

          40486

          40278

          2003

          2001

          2004

          2005

          2006

          2009

          2010

          ];

            • Re: Date Problem

              Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )

              please explain it.

               

              Thanks,

              Ritul

                • Re: Date Problem
                  Stefan Wühl

                  Just noticed, that you don't really need the alt() function here, reason being that you need to check the numeric value in a condition anyway:

                   

                  if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol))) as DateColNew

                   

                   

                  Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )

                  please explain it.

                   

                  If your numeric value is smaller than 2200, I assume it's describing a year (first argument to makedate() function), if it's larger than 2200, it's a date in numeric representation, so we just need to format the text representation.