topic Re: fYear not found error whilst creating calendar in QlikView

fYear not found error whilst creating calendar

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm using the following to create the dual and fiscal calendar but I am receiving the following error. any assistance appreciated.

Error = Field not found - <fYear>

..........................................................................

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(%DateEmpFormAdded) as minDate,
max(%DateEmpFormAdded) as maxDate
Resident EmployersBoard;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
Set vMonthFactor = 7;
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Set vMonthFactor = 7;

Calendar:
Load
TempDate AS %DateEmpFormAdded,
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Year + If(Month>=$(vMonthFactor), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1 as fMonth, // Numeric fiscal month

Year(TempDate) as Year, // Your standard master calendar
Month(TempDate) as Month

Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: fYear not found error whilst creating calendar

hic — Wed, 06 Jul 2016 07:58:08 GMT

The behaviour is correct. There is no fYear in available where you reference it. You can only use the fields available in the input table defined by "Resident TempCalendar".

However, if you use preceding Load, you can create a step-wise definition of your fields. See Preceding Load.

HIC

Re: fYear not found error whilst creating calendar

Anonymous — Wed, 06 Jul 2016 09:15:19 GMT

Hi Henric,

I did read the link but am still struggling with this. Are there examples that others have used that you can please direct me to so I can get a real example?

Cheers

Laura

Re: fYear not found error whilst creating calendar

hic — Wed, 06 Jul 2016 10:05:52 GMT

Try

Load
TempDate AS %DateEmpFormAdded,
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load
Year + If(Month>=$(vMonthFactor), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1 as fMonth, // Numeric fiscal month
*;
Load
Year(TempDate) as Year, // Your standard master calendar
Month(TempDate) as Month,
*
Resident TempCalendar

Re: fYear not found error whilst creating calendar

hic — Wed, 06 Jul 2016 10:07:37 GMT

Re: fYear not found error whilst creating calendar

tyagishaila — Wed, 06 Jul 2016 10:56:08 GMT

In Calendar Table You have to use preceding load to use the field fYear and fMonth

Calendar:

Load *, Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month;

Load *,

Year + If(Month>=$(vMonthFactor), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1 as fMonth, // Numeric fiscal month;
Load
TempDate AS %DateEmpFormAdded,
Year(TempDate) as Year, // Your standard master calendar
Month(TempDate) as Month

Resident TempCalendar

Re: fYear not found error whilst creating calendar

Anonymous — Thu, 07 Jul 2016 02:48:49 GMT

Thank-you Henric.

I am using the calendar from the script (from hyperlink you gave me in last reply). I have chosen to use the fast method for Min/Max. My calendar is not linking to my table (date field = '%DateEmpFormAdded'). My script so far is below.

Questions please:-

In Step 1. you mention to start by Loading your facts table and to Use Floor(Date) to define the Date key !!" . My date field is '%DateEmpFormAdded' . The facts table becomes an island table. I dont understand where to add the Date field ?

...........................

Concatenate(EmployersBoard)
LOAD
EmpState
,'Lead' as Type
,ItemCreatedWhen as %DateEmpFormAdded

...................Script for Calendar.................

Set vNumberOfYears = 2 ;
Facts:
Load
Date(Floor(MakeDate(2017)-365.2424*$(vNumberOfYears)*Rand())) as Date
Autogenerate 1000;

// ========= Fast method, using symbol tables =============
MinMaxDate:
Load
Num(Min(Fieldvalue('%DateEmpFormAdded',RecNo()))) as MinDate,
Num(Max(Fieldvalue('%DateEmpFormAdded',RecNo()))) as MaxDate
Autogenerate FieldValueCount('%DateEmpFormAdded');

Let vMinDate = Peek('MinDate',0,'MinMaxDate');
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');
Drop Table MinMaxDate;
// --- The Fiscal Calendar --------------------------------------
MonthBasedFiscalCalendar:
Load // --- Grain = Year ---------------
Dual(If($(vCal_FM)=1,Year,(Year-1) &'/'& Year),Year)
as Fiscal.Year,
YearStart as Fiscal.YearStart,
// --- Grain = Quarter ------------
Dual('Q' & Quarter, Quarter) as Fiscal.Quarter,
QuarterStart as Fiscal.QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart)
as Fiscal.YearQuarter,
// --- Grain = Month --------------
Dual(Month(Date),Month) as Fiscal.Month,
MonthStart(Date) as Fiscal.MonthStart,
Date(MonthStart(Date),'YYYY MMM') as Fiscal.YearMonth,
Mod(Month-1,3)+1 as Fiscal.MonthOfQuarter,
// --- Grain = Day ----------------
Date - YearStart + 1 as Fiscal.DayOfYear,
Date - QuarterStart + 1 as Fiscal.DayOfQuarter,
Day(Date) as Fiscal.DayOfMonth,
Date;
Load // --------------------------------
Year(YearEnd) as Year,
Ceil(Month/3) as Quarter,
*;
Load // --------------------------------
AddMonths(YearStart(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1) as YearStart,
AddMonths(YearEnd(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1) as YearEnd,
AddMonths(QuarterStart(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1) as QuarterStart,
Mod(Month(Date)-$(vCal_FM), 12)+1 as Month,
Date;
Load // --------------------------------
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;

Regards,