topic Re: derive quarter and month values in QlikView

derive quarter and month values

Wed, 29 Oct 2014 00:07:58 GMT

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]);

Re: derive quarter and month values

Wed, 29 Oct 2014 00:21:28 GMT

Also my W_END_DT has format as 12-Feb-14

Attached my qvw

Re: derive quarter and month values

JonnyPoole — Wed, 29 Oct 2014 00:29:11 GMT

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]);

Re: derive quarter and month values

Wed, 29 Oct 2014 00:41:29 GMT

Thanks for your reply.

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

Re: derive quarter and month values

JonnyPoole — Wed, 29 Oct 2014 01:14:10 GMT

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]);

Re: derive quarter and month values

MarcoWedel — Wed, 29 Oct 2014 09:52:09 GMT

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

Re: derive quarter and month values

Wed, 29 Oct 2014 09:55:21 GMT

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]);