5 Replies Latest reply: Jan 5, 2010 10:11 AM by pabloec RSS

    Can´t extract year and month from date field

    pabloec

      hi,

      When i load a crosstable the date is loaded in a text format (not date format), so i load the table again using resident to take date in a date and number format. Finally i drop the original crosstable.

      I need to extract the year and the month from the date field but when i reload the script it gives me an error becouse the date is taken as a text.

       

      Format of crosstable:

       

      SEÑAL

      Unidad

      01/01/2009

      01/02/2009

      01/03/2009

      AMERICA SPORTS

      P

      0,06

      0,06

      0,06

      AMERICA SPORTS

      Q

      112.553

      112.553

      112.553

      CANAL (A)

      P

      0,140

      0,140

      0,140

      CANAL (A)

      Q

      112.553

      112.553

      112.553

       

      Where fields in blue are DATE for the cross table; and the columns SEÑAL and Unidad are the Qualifiers.

       

       

       

      This is what i´m trying to do in the script:

       

      Load

      *,

      Year(DATE) AS YEAR,

      Month(DATE) AS MONTH

      ;

       

      Q1:

      CrossTable(DATE, Q, 3)

      LOAD * FROM

      [C:\ budget afiliates 2009.xls]

      (biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])

      WHERE(Unidad = 'Q');

       

       

      CANTIDADPESOS:

      LOAD DATE(NUM#(FECHA)) AS DATE,

      Q AS BUDGET

      RESIDENT Q1;

       

      DROP TABLE Q;

       

       

      This is the script error:

       

      "

      Field not found - <DATE>

      Q1:

      CrossTable(DATE, Q, 3)

      LOAD * FROM

      . . . "

       

       

      What can i do to can extract year and month from this table?

       

      I tried to do this but it doesn´t work:

      Q1:

      CrossTable(DATE, Q, 3)

      LOAD * FROM

      [C:\ budget afiliates 2009.xls]

      (biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])

       

      WHERE(Unidad = 'Q');

       

       

      CANTIDADPESOS:

      LOAD DATE(NUM#(FECHA)) AS DATE,

      Q AS BUDGET

      RESIDENT Q1;

       

      Load

      *,

      Year(DATE) as YEAR,

      Month(DATE) as MONTH

      ;

       

      DROP TABLE Q;

       

       

       

      But when i load the next parto of the crosstable it gives me the same error:

       

       

      "

      Field not found - <DATE>

      P1:

      CrossTable(DATE, P, 3)

      LOAD * FROM

      . . . "

      THANKS,