derive quarter and month values

[#1]

How to derive quarter and month values in the code below?

M_data:

LOAD E_Code,

     W_END_DT,     

     [M data]    

FROM

[Mdata.xlsx]

(ooxml, embedded labels, table is [MData]);

[#2]

Also my W_END_DT has format as 12-Feb-14

Attached my qvw

[JonnyPoole · Accepted solution]

You can try

month(date(W_END_DT)) as month

'Q' & ceil( month(date(W_END_DT)) / 3) as quarter

M_data:

LOAD E_Code,

     W_END_DT,    

     month(date(W_END_DT)) as month,

     'Q' & ceil( month(date(W_END_DT)) / 3) as quarter,

     [M data]   

FROM

[Mdata.xlsx]

(ooxml, embedded labels, table is [MData]);

[#4]

Thanks for your reply.

I attached my qvw and excel to the above reply....i couldnt get the desired result

[JonnyPoole]

Thanks a lot. To read the format use date#() inside the other functions and specify the incoming format 'DD-MMM-YY' as follows.  It should work now. Also i applied the same to WEEK_END_DT so that it is recognized as a date field with full functions available

Management_data:

LOAD Eng_Code,

     date(date#(WEEK_END_DT,'DD-MMM-YY')) as WEEK_END_DT ,

     month(date(date#(WEEK_END_DT,'DD-MMM-YY'))) as month,

     'Q' & ceil( month(date(date#(WEEK_END_DT,'DD-MMM-YY'))) / 3) as quarter,   

     [Management data]     

FROM

[Management data.xlsx]

(ooxml, embedded labels, table is [Management Data]);

[MarcoWedel]

Hi,

one solution could be also:

Management_data:
LOAD *,
    Day(WEEK_END_DT) as Day,
    WeekDay(WEEK_END_DT) as WeekDay,
    Week(WEEK_END_DT) as Week,
    WeekName(WEEK_END_DT) as WeekName,
    Month(WEEK_END_DT) as Month,
    MonthName(WEEK_END_DT) as MonthName,
    'Q'&Ceil(Month(WEEK_END_DT)/3) as Quarter,
    QuarterName(WEEK_END_DT) as QuarterName,
    Year(WEEK_END_DT) as Year,
    WeekYear(WEEK_END_DT) as WeekYear;
LOAD Eng_Code,
    Date#(WEEK_END_DT,'DD-MMM-YY') as WEEK_END_DT,   
    [Management data]
FROM [Management data.xlsx] (ooxml, embedded labels, table is [Management Data]);

hope this helps

regards

Marco

[#7]

Hi,

M_data:

LOAD E_Code,

     W_END_DT,

   Month(W_END_DT) as Month,

    'Q'& ceil(num(month(W_END_DT))/3) as Quarter,    

     [M data]    

FROM

[Mdata.xlsx]

(ooxml, embedded labels, table is [MData]);