Changing field values

[#1]

Hi

I have load the following table

TASKS:

CrossTable(CalendarMonth, Value, 4)

LOAD UNIQUE_ID as TASK_HKEY,

     PARENT_ID,

     TASK_NAME,

     Resource as RESOURCE,

     ACC_MAY as 'May',

     ACC_JUN as 'Jun',

     ACC_JUL as 'Jul',

     ACC_SEP as 'Sep'

FROM

[project data 20131029.xlsx]

How can I change the value to be an actual calender month instead of text e.g. 'May'

Thanks

Mpumi

[jonathandienst]

Mpumi

Something like this:

TASKS:

LOAD *,

  Date(Date#(Year(Today()) & CalendarMonthName, 'YYYYMMM')) As CalendarMonth

;

CrossTable(CalendarMonthName, Value, 4)

LOAD UNIQUE_ID as TASK_HKEY,

     PARENT_ID,

     TASK_NAME,

     Resource as RESOURCE,

     ACC_MAY as 'May',

     ACC_JUN as 'Jun',

     ACC_JUL as 'Jul',

     ACC_SEP as 'Sep'

FROM

[project data 20131029.xlsx]

HTH

Jonathan

[Anonymous]

Hi,
You can put a date instead of the month and then create a calendar:

TASKS:

CrossTable(CalendarMonth, Value, 4)

LOAD UNIQUE_ID as TASK_HKEY,

     PARENT_ID,

     TASK_NAME,

     Resource as RESOURCE,

     ACC_MAY as '01/05/2013',

     ACC_JUN as '01/06/2013',

     ACC_JUL as '01/07/2013',

     ACC_SEP as '01/09/2013'

FROM

[project data 20131029.xlsx]

CALENDAR:
LOAD DISTINCT
  CalendarMonth,
  Month(Date(CalendarMonth))   as Month,
  Year(Date(CalendarMonth))   as Year,
  'Q'&Ceil(num(Month(Date(CalendarMonth)))/3) as Quarter
RESIDENT
TASKS
;

Then you can use the field Month

[#4]

thanks

[#5]

thanks