Error during dynamic calendar creation

[#1]

I get this error,  Can someone help me to find the reason.  Thanks

Field not found - <<=>

TempCalendar:

LOAD  + rowno() - 1 AS Num,

     date( + rowno() - 1) AS TempDate

AUTOGENERATE 1

While +IterNo()-1<=

-------

I have this as the Date calculation for quarter/calendar.

----------

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q1

4, Q2

5, Q2

6, Q2

7, Q3

8, Q3

9, Q3

10, Q4

11, Q4

12, Q4

];

Temp:

Load

               min(DATA_AS_OF) as minDate,

               max(DATA_AS_OF) as maxDate

Resident HRInfo;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

//LET varMinDate = Num(MakeDate(2010,1,1));                  //first date

//LET varMaxDate = Floor(num(Today()));                       // will find the last record

LET varToday = Num(today());                                                                                // Finds todays date

//*************** Temporary Calendar ***************

TempCalendar:

LOAD $(varMinDate) + rowno() - 1 AS Num,

     date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE 1

While $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS DATA_AS_OF,

     Week(TempDate) AS Week,

     Year(TempDate) AS Year,

     Month(TempDate) AS Month,

       Day(TempDate) AS Day,

     Weekday(TempDate) AS WeekDay,

     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

     yearname ( TempDate, 0, 10 ) as FiscalYear,

     Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     Month(TempDate)&'-'&right(year(TempDate),10) as FiscalMonthYear,

     Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

     inyeartodate(TempDate, $(varToday), 0, 10) * -1   AS CurYTDFlag,

     inyeartodate(TempDate, $(varToday), -1, 10) * -1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[jagan]

Hi,

I think the issue is with the variables, check that the variables are not getting values

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Try with this statements

Trace varMinDate ;

Trace varMaxDate ;

Debug and check whether this two variables are getting values, if not check check HRInfo table.

Regards,

Jagan.

[Gysbert_Wassenaar]

As you can see the variables varMinDate and varMaxDate are empty. Check that your DATA_AS_OF field contains numeric date values. If you try to use the min and max functions on strings you won't get the values your after. You can use the date# function if you need to convert a date string into a numeric date.

[#4]

Let varMinDate = Num(Peek('datefield', 0, 'Temp'));

Let varMaxDate = Num(Peek('datefield', 0, 'Temp'));

Here you need to use a date field

[Anonymous · Accepted solution]

Or maybe the resident table HRInfo is empty (?)