Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Changing field values

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

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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


Not applicable
Author

thanks

Not applicable
Author

thanks