topic Re: Calendar in QlikView

Calendar

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am having an issue with the script below to create a temp calendar. Strangely, it used to work but now it is coming with an error message.Not sure how to resolve it.

Temp:

Load

Min(SaleDate) as MinDate,

Max(SaleDate) as MaxDate

Resident SALES;

Let vMinDate = Num(Peek('MinDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:

LOAD

$(vMinDate) + Iterno()-1 As Num,

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

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

DROP Table Temp;

Thanks

Shamit

Re: Calendar

Frank_Hartmann — Sat, 02 Jul 2016 06:00:21 GMT

Try like this:

TempCalendar:

LOAD

$(vMinDate) + RowNo() - 1 AS Num,

Date($(vMinDate) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

hope that helps

Re: Calendar

sorrakis01 — Sat, 02 Jul 2016 06:59:19 GMT

HI,

It works:

SALES:

LOAD * INLINE [

SaleDate

10/01/2016

30/03/2016

];

Temp:

Load

Min(SaleDate) as MinDate,

Max(SaleDate) as MaxDate

Resident SALES;

Let vMinDate = Num(Peek('MinDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:

LOAD

$(vMinDate) + Iterno()-1 As Num,

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

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

DROP Table Temp;

Regards

Re: Calendar

shamitshah — Sat, 02 Jul 2016 11:54:11 GMT

Hi Jordi,

Thanks , I tried it again and it appears to work.

I think the issue I had was that SALES table had no values due to an incorrect filter.

Shamit

Re: Calendar

andrespa — Tue, 17 Feb 2026 16:37:41 GMT

Having this some issue and I've been trying for last 2 hours and I'm not able to find a solution. It's clear that the problem is due to the no evaluation of the variables, but I see that the values get properly stored in the temp table, but then for some reason these 2 lines don't retrieve me the desired values:

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

Any idea what could be happening? It has to be maybe with the date format?

Best Regards,

Andrés

Re: Calendar

marcus_sommer — Wed, 18 Feb 2026 15:23:54 GMT

It's properly related to the comma as decimal delimiter which is valid within a table because it's there just a formatting but the variable takes this value directly and this is then no valid number by calling the variable.

If you don't need the time-part then you could cut it with floor() and if the information is wanted the num() could be adjusted, maybe like: num(Value, '.', '') or a replace() is used.

Re: Calendar

andrespa — Thu, 19 Feb 2026 09:46:49 GMT

Hi Marcus,

I feel the solution goes in this direction. But even with the floor() function for some reason the number doesn't get evaluated in the variable:

Re: Calendar

marcus_sommer — Thu, 19 Feb 2026 09:59:48 GMT

You are calling the wrong fields within the peek() because you have a qualify-statement before the TempCalendar and therefore the fields are: TempCalendar.MinDate & TempCalendar.MaxDate.

You may adjust the call or probably better removing the qualify. I suggest to avoid it in general because it's causing usually much more trouble and efforts as it's be useful.

Re: Calendar

andrespa — Thu, 19 Feb 2026 10:09:17 GMT

Hi Marcus, true. I've forgot about the qualify. However, after removing it and double check the names, it's still not working:

Re: Calendar

marcus_sommer — Thu, 19 Feb 2026 10:22:02 GMT

Try the variable-assignment without the num() - floor() and min/max in the load are removing each formatting from the values and therefore the num() isn't necessary.

Re: Calendar

andrespa — Thu, 19 Feb 2026 10:38:38 GMT

This is driving me crazy, it's seems a really basic stuff but I'm not able to understand what's going on. Still not evaluating the variable:

Re: Calendar

marcus_sommer — Thu, 19 Feb 2026 11:53:00 GMT

What's the way to view the variable-content? Maybe there is something wrong. What about with this:

trace min = $(vMinDate) & max = $(vMaxDate);

directly after the assignment?

Further take a look if there are more places where such logic are applied maybe overwriting the previous results. Helpful may also be to query the temp-table again, like:

check: load *, rowno() as RowNo resident TempCalendar;

Does it fail or showing more as one record or is in other ways unexpected?

Re: Calendar

andrespa — Thu, 19 Feb 2026 12:29:23 GMT

Solved. It was indeed an incorrect way of showing the variables on the trace. The actual solution was the floor().

Thanks Marcus, you've helped me a lot!