topic Calendar Issues in QlikView

Calendar Issues

guya — Mon, 19 Oct 2009 19:18:46 GMT

Hi,

I'm using two calendars (first based on orders date and second one on customer registration date). Order date calendar works fine but the registration calendar display years since 1900!

Here is the script:

//Define variable of the minimum Date for Registration
LET varMinRegDate = Num (Peek ('mreg', 0,'Customers'));

//Define variable of the maximum Date for Registration
LET varMaxRegDate = Num (Peek ('mreg', -1,'Customers'));

//******************** Temporary Calendar for Registration **********************

TempRegCalendar:
LOAD
$(varMinRegDate)+IterNo()-1 as Num,
Date($(varMinRegDate)+IterNo()-1) as TempRegDate
AUTOGENERATE (1) WHILE $(varMinRegDate)+IterNo()-1 <= $(varMaxRegDate);

RegCalendar:
LOAD DISTINCT
TempRegDate as mreg,
Year (TempRegDate) as RegYear,
Month (TempRegDate) as RegMonth,
num (Month (TempRegDate), '00') as RegMonthNum,
Week (TempRegDate) as RegWeek,
Day (TempRegDate) as RegDay,
WeekDay (TempRegDate) as RegWeekDay,
applymap ('QuartersRegMap', num (Month (TempRegDate), '00'), 'no quarter') as QuartersReg,
Date (monthstart(TempRegDate), 'MM-YYYY') as RegMonthYear
RESIDENT TempRegCalendar ORDER BY TempRegDate ASC;

// TempCalendar was used to created the TempDate. TempCalendar is now obsolete
DROP TABLE TempRegCalendar;

Thx in advance for your help

Guy

Calendar Issues

Mon, 19 Oct 2009 19:28:51 GMT

Hi Guy,

it seems that your variables didn´t contain the values you expected to create your registration calendar.

Control the variables :

- Settings

- list of variables

Good luck!

Rainer

Calendar Issues

guya — Mon, 19 Oct 2009 19:51:48 GMT

Hello Rainer,

Thx for your prompt reply, Indeed the varMinRegDate is empty! Stop me if i'm wrong but the system is supposed to fill those variables during the loading?

How can I fix that?

Best,

Guy

Calendar Issues

Mon, 19 Oct 2009 20:05:09 GMT

Guy,

we are able to have a fix start date of an calendar or we fill variables dynamically during script execution.

Qustion of interest here is: Why is your variable empty?

Run your script using the debugger could help to find out why.

Or:

Are you able to strip down your source and place an example here in the forum so that we can work with it?

Rainer

Calendar Issues

Mon, 19 Oct 2009 20:07:49 GMT

Hi,

I had a similar issue wherein the peek is returning null for the first record and the issue was the referenced field was having a blank in the data set.

try with the following and check whether it returns the second date onwards:

//Define variable of the minimum Date for Registration
LET varMinRegDate = Num (Peek ('mreg', 1,'Customers'))

--Arun

Calendar Issues

guya — Mon, 19 Oct 2009 20:50:08 GMT

Hi Arun,

I found some null records and delete them. it works fine now.

Also the expected values are set for variables.

Thank you all for the help.

Guy

Calendar Issues

Mon, 19 Oct 2009 21:02:56 GMT

Cheers! Happy Qlicking...

--Arun