topic Re: Master Calendar loop in QlikView

Master Calendar loop

Mon, 16 Jun 2014 10:13:22 GMT

I am trying to create a master Calendar to use within my Qlikview application. I have used the script provided by Josh Good in Creating A Master Calendar.

This is the min and max date values i get:

varMaxDate	41806,475196759
varMinDate	40909,0028125

This is the part that keeps looping, i haven't checked when this eventually stops(i guess when Qlikview is out of memory).

This has looped to many millions before i just cancel it. Anyone know what i am doing wrong?

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

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

Re: Master Calendar loop

MK_QSL — Mon, 16 Jun 2014 10:14:29 GMT

What is you MinDate and MaxDate?

Re: Master Calendar loop

jagannalla — Mon, 16 Jun 2014 10:24:23 GMT

Hi,

This code is working for me. Can you please attach sample file.

LET varMaxDate =	41806.475196759;
LET varMinDate=	40909.0028125;

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

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

Cheers!!
Jagan

Re: Master Calendar loop

its_anandrjs — Mon, 16 Jun 2014 10:31:50 GMT

Your code is correct instead of the variable declaration remove , from the value and put .

Try with this

LET varMaxDate	= 41806.475196759;
LET varMinDate	= 40909.0028125;

And then use your code

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

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

Re: Master Calendar loop

SunilChauhan — Mon, 16 Jun 2014 10:42:51 GMT

only you need to change varMaxDate and VarMinDate

you can use lik below

let varMinDate=Makedate(2010,1,1);

let varMaxDate=Date( today());

hope this helps

Re: Master Calendar loop

Mon, 16 Jun 2014 10:42:52 GMT

I did work when i commented out this part, and "hardcoded" the min and max values. i don't really see the difference since the Tempcalendar part only uses varMinDate and varMaxDate, and when debugging they seem correctly set with the same values as when i "hardcoded" them.

//Temp:

//Load

// min(FileSent) as minDate,

// max(FileSent) as maxDate

//Resident filesTable;

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

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

//DROP Table Temp;

Let varMinDate = 40909.0028125;

Let varMaxDate = 41806.475196759;

Re: Master Calendar loop

senpradip007 — Mon, 16 Jun 2014 10:44:55 GMT

Can you upload sample qvw?

Re: Master Calendar loop

SunilChauhan — Mon, 16 Jun 2014 10:45:32 GMT

see my post and put date like in post

Re: Master Calendar loop

Mon, 16 Jun 2014 10:48:25 GMT

try this:

Temp:

min(FileSent) as minDate,

max(FileSent) as maxDate

Resident filesTable;

///******Create min/max variables*********

Let vMinDate = num(peek('minDate',0, 'Temp'));

Let vMaxDate = num(peek('maxDate', 0, 'Temp'));

Drop Table Temp;

////*******Temp Calendar******

DateField:

Load

date($(vMinDate) + RowNo() -1) as TempDate

AutoGenerate

$(vMaxDate) - $(vMinDate) +1;

Re: Master Calendar loop

Mon, 16 Jun 2014 10:50:08 GMT

This appears to be down to your number format change that you should be fine

Re: Master Calendar loop

its_anandrjs — Mon, 16 Jun 2014 10:53:58 GMT

You can try this way also

Temp:

Load

min(FileSent) as minDate,

max(FileSent) as maxDate

Resident filesTable;

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

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

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

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

DROP Table Temp;

Re: Master Calendar loop

Mon, 16 Jun 2014 10:58:22 GMT

How do you suggest i cast this? Is it a function i can use to force it to the right format?

Re: Master Calendar loop

Mon, 16 Jun 2014 10:58:39 GMT

Make sure your date format is correct from your source table

eg like:

DATE(FLOOR(Timestamp#(fileSent,'YYYY-MM-DD hh:mm:ss')),'YYYY-MM-DD') as Date_Clean

date(date#(fileSent),'YYYY-MM-DD') as fileSent

you date needs to be YYYY-MM-DD or whatever your format is like.

Re: Master Calendar loop

Mon, 16 Jun 2014 11:06:05 GMT

The column in the database is DATETIME. This i assume qlikview would automatically format this correctly.

Or do i need to "force" the field when loading to use Timestamp value?

Can you only use Date value in the master calendar script? Not a timestamp?

This is how it is loaded: Timestamp(FileSent) AS FileSent

Re: Master Calendar loop

jagannalla — Mon, 16 Jun 2014 11:09:27 GMT

use date function

LET varMaxDate =	Date('41806.475196759');
LET varMinDate=	Date('40909.0028125');

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

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

Re: Master Calendar loop

Mon, 16 Jun 2014 11:09:51 GMT

No reason to use timestamp when the mastercalendar only creates datefields. So no i would not recommend timestamp along with it.

If you need the timestamp then use another alias. Best practice would be to convert it to only date. So i would recommend that you convert it to YYYY-MM-DD or what you use. Then you would not get any errors.

And dont convert the variables or min/max - Do it in your source load!!!

Re: Master Calendar loop

Mon, 16 Jun 2014 11:26:50 GMT

Furthermore if you remove timestamp (If you dont use it) the performance will increase due too that there are less unique values in your dataset. So it can be more compressed

Re: Master Calendar loop

Mon, 16 Jun 2014 11:58:29 GMT

It worked as expected when i transformed the Timestamp to a Date field.