topic Re: Concatenating Year and Month in QlikView

Concatenating Year and Month

Anonymous — Mon, 01 Dec 2014 23:07:00 GMT

I have the following calendar script:

SUB CalendarGen(vFieldName,vTableName,vPrefix,vGeneric)

/*
Calendar Generation Sub-routine

Parameters:
vFieldName - The field name of the date that a calendar should be based upon
vTableName - The name of the table where the date field resides
vPrefix - The prefix of choice for date related field name generation, e.g. vPrefix = 'Sales' leads to field names of the format 'Sales MonthYear', 'Sales Year' etc.
vGeneric - Optional parameter. 1 (default) = prepare for generation of a standalone calendar which is of benefit when analysing different aspects of time in one chart. 0 = Turn off generic calendar preparation.

v1.0, 16-Sep-2008, Author: Jonas Valleskog, QlikTech
v2.0, 17-Mar-2014, Author: Jonas Valleskog, ContextBI

Notes: Generic calendar generation script that enables scalable handling of creating and navigating multiple date fields

Implementation notes:
1. Call the SUB procedure once per date field
2. Copy or re-create the calendar objects (time related list boxes) laid out in the front-end of this example QVW file

Optimisation notes:
1. To avoid potentially slow queries against large in-memory tables, contemplate storing out the date field to QVD first and use the QVD store as the input source to the MinMax: table creation.
2. If gaps in calendars for missing dates are not an issue, consider replacing AUTOGENERATE() logic for generating the calendar table with a distinct list of each date seen in the source table instead.
*/

IF IsNull(vGeneric) THEN
SET vGeneric = 1;
ENDIF

MinMax:
LOAD Max([$(vFieldName)]) AS MaxDate,
Min([$(vFieldName)]) AS MinDate
RESIDENT [$(vTableName)];

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',-1,'MinMax'));
LET vToday = Num(Today());

// Quarter map generation
QuarterMap:
MAPPING LOAD * INLINE [
     Month, Quarter
     1, Q1
     2, Q1
     3, Q1
     4, Q2
     5, Q2
     6, Q2
     7, Q3
     8, Q3
     9, Q3
     10, Q4
     11, Q4
     12, Q4
];

// Calendar Generation

IF vPrefix = 'Generic' THEN

SET vPrefix=;

ELSEIF vGeneric = 1 THEN

Calendars:
LOAD
'$(vPrefix)' AS Calendar
AUTOGENERATE(1);

LET vPrefix=vPrefix & ' ';

[Generic Calendar]:
LOAD DISTINCT
Date($(vMinDate) + RecNo() - 1) AS [Date]
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

ENDIF

[$(vPrefix)Calendar]:
LOAD *,
autoNumber([$(vPrefix)MonthYear],'$(vPrefix)MonthYear') AS [$(vPrefix)numMonthYear],
autoNumber([$(vPrefix)QuarterYear],'$(vPrefix)QuarterYear') AS [$(vPrefix)numQuarterYear],
autoNumber([$(vPrefix)WeekYear],'$(vPrefix)WeekYear') AS [$(vPrefix)numWeekYear],
AutoNumber([$(vPrefix)Year] & [$(vPrefix)Quarter], 'QuarterID') as [QuarterID];

LOAD Distinct
[$(vFieldName)],
num([$(vFieldName)]) AS [num$(vFieldName)],
Week([$(vFieldName)]) AS [$(vPrefix)Week],
Year([$(vFieldName)]) AS [$(vPrefix)Year],
Month([$(vFieldName)]) AS [$(vPrefix)Month],
Day([$(vFieldName)]) AS [$(vPrefix)Day],
WeekDay([$(vFieldName)]) AS [$(vPrefix)WeekDay],
applyMap('QuarterMap', num(month([$(vFieldName)])),null()) AS [$(vPrefix)Quarter],
Date(monthStart([$(vFieldName)]), 'MMM-YYYY') AS [$(vPrefix)MonthYear],
dual(applyMap('QuarterMap', num(month([$(vFieldName)])),null())
& '-' & Year([$(vFieldName)]),QuarterStart([$(vFieldName)])) AS [$(vPrefix)QuarterYear],
dual(Week([$(vFieldName)]) & '-' & Year([$(vFieldName)]),WeekStart([$(vFieldName)])) AS [$(vPrefix)WeekYear],
if(Year2Date([$(vFieldName)], 0, 1, $(vToday)),1) AS [$(vPrefix)YTD Flag],
if(Year2Date([$(vFieldName)], -1, 1, $(vToday)),1) AS [$(vPrefix)PYTD Flag];
LOAD
Date($(vMinDate) + RecNo() - 1) AS [$(vFieldName)]
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

DROP TABLE MinMax;

END SUB

I want an expression to create the dimension key "Period" by concatenating the year and month into a single number within this calendar routine. For example, October 2011 will be stored as 201110. Can you assist

Regards

Re: Concatenating Year and Month

oscar_ortiz — Mon, 01 Dec 2014 23:14:58 GMT

You can try adding:

LOAD Distinct
[$(vFieldName)],

num(Year([$(vFieldName)]) & num(Month([$(vFieldName)]),'00') ) AS [$(vPrefix)Year Month],

num([$(vFieldName)]) AS [num$(vFieldName)],
Week([$(vFieldName)]) AS [$(vPrefix)Week],
Year([$(vFieldName)]) AS [$(vPrefix)Year],
Month([$(vFieldName)]) AS [$(vPrefix)Month],
Day([$(vFieldName)]) AS [$(vPrefix)Day],

Good luck

Oscar

Re: Concatenating Year and Month

MarcoWedel — Tue, 02 Dec 2014 00:07:26 GMT

Date(monthStart([$(vFieldName)]), 'YYYYMM') AS[$(vPrefix)Period],

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 00:22:56 GMT

Many thanks Marco

I did that and i get the following error messages:

Thank you

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 00:32:56 GMT

Hi Oscar

Many thanks, i am trying it out and will let you know how it goes.

Regards

Re: Concatenating Year and Month

MarcoWedel — Tue, 02 Dec 2014 00:33:22 GMT

There is either a from clause missing in the line before LOAD Distinct or a ';' (preceding load)

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 00:53:01 GMT

Many thanks Marco, i will look into it.

Regards

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 01:54:20 GMT

Hi Oscar

Script loads well. I am getting the following display in the year list box:

The date actually start from the number 13 to 2014. What could be causing this? How can i limit the years displayed to say the last 5 years?

Re: Concatenating Year and Month

oscar_ortiz — Tue, 02 Dec 2014 23:30:37 GMT

The calendar is being built by the values stored in the table/field that you are passing to the function.

The function is going and getting the Min and Max values of the date field you are selecting.

You would want to limit the data being read initially into the table.

Good luck

Oscar

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 23:41:24 GMT

Hi Oscar

I managed to limit the dates loaded to the last five years. Thanks

Regards.

Chris

Re: Concatenating Year and Month

Anonymous — Tue, 02 Dec 2014 23:48:27 GMT

Very confusing.

Generate Master Calendar in this case outside qlik.