topic Re: Changing field values in QlikView

Changing field values

Tue, 14 Jan 2014 10:42:38 GMT

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

Re: Changing field values

jonathandienst — Tue, 14 Jan 2014 10:49:30 GMT

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

Re: Changing field values

Anonymous — Tue, 14 Jan 2014 10:59:13 GMT

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

Re: Changing field values

Wed, 15 Jan 2014 11:46:11 GMT

thanks

Re: Changing field values

Wed, 15 Jan 2014 11:46:30 GMT

thanks