topic Re: Error during dynamic calendar creation in QlikView

Error during dynamic calendar creation

Fri, 18 Oct 2013 15:06:36 GMT

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;

Re: Error during dynamic calendar creation

jagan — Fri, 18 Oct 2013 16:03:45 GMT

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.

Re: Error during dynamic calendar creation

Gysbert_Wassenaar — Fri, 18 Oct 2013 16:04:25 GMT

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.

Re: Error during dynamic calendar creation

Fri, 18 Oct 2013 16:11:10 GMT

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

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

Here you need to use a date field

Re: Error during dynamic calendar creation

Anonymous — Fri, 18 Oct 2013 17:43:22 GMT

Or maybe the resident table HRInfo is empty (?)