topic Re: master calendar in QlikView

master calendar

mcornips — Thu, 21 Nov 2013 17:57:04 GMT

When doing a reload I get the following error:

Script line error:

LET vToday = 41488,655589271

This is the MaxDate that seems not to work.

Does anybody have an idea what this could be?

Regards,

Maurice

Re: master calendar

Bill_Britt — Thu, 21 Nov 2013 18:00:19 GMT

It is hard to tell without seeing the script. It should look like this:

varMaxDate = num(Peek('Fieldname',-1,'TableName'));

vToday= num(Today());

Bill

Re: master calendar

mcornips — Thu, 21 Nov 2013 18:03:25 GMT

this is the script:

Re: master calendar

MayilVahanan — Thu, 21 Nov 2013 18:10:07 GMT

There is no issues in tat. Am guessing you used MinMax as tableName;

May be try like this

LET vToday = Floor($(vMaxDate)); // For Get the number without decimal

Can you post full script?

Re: master calendar

mcornips — Thu, 21 Nov 2013 18:14:56 GMT

LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Facts;

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

TempCal:
LOAD
Date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;

MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;

Re: master calendar

MayilVahanan — Thu, 21 Nov 2013 18:18:25 GMT

Have doubt, why you need a separate variable vToday to store the MaxDate, you can use same variable in many place know?

Re: master calendar

mcornips — Thu, 21 Nov 2013 18:35:38 GMT

FLOOR seems to work Mayil, but I have to use it in the first LOAD-statement.

Like this:

LOAD
Min(Date) as MinDate,
FLOOR (Max(Date)) as MaxDate
RESIDENT Facts;

Thank you,

Maurice

Re: master calendar

MayilVahanan — Thu, 21 Nov 2013 18:51:37 GMT

Glad to fix the issues.

Re: master calendar

Bill_Britt — Thu, 21 Nov 2013 18:57:57 GMT

Here is another reason why it will not work

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

the Peek function states the the "0" is the first recond and you are using it for both. On the MaxDate you need to use "-1" which is the Last record.

Bill

Re: master calendar

mcornips — Thu, 21 Nov 2013 19:05:45 GMT

I don't think this is relevant in this respect because there is only 1 record in this table called 'MinMax'. This record has 2 fields: MinDate and MaxDate. The relevant values for both fields are stored in the first and only record. Using -1 gives me the same result.