Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

oscarmqz
Contributor III

Create dates in script

Hi, I'm trying to create a calendar dimension containing all the possible dates between a min(date) and max(date), I don't knwo if the way I'm doing it si correct / possible? It doesn't fail when refreshing but TIMEDIM table is not created.

Dates:
LOAD date(max(ORDERS.SO_DTL_CRT_DT)) as MaxDate,
date(min(ORDERS.SO_DTL_CRT_DT)) as MinDate
Resident ORDERS;

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

DROP Table Dates;

TIMEDIM:
LOAD $(vMinDate) + (IterNo()-1) as Date
while $(vMinDate)+ (IterNo()-1)<= $(vMaxDate);

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Create dates in script

The basic issue that I can see is formatting your min and max values as dates - the date values don't work as well in the following logic. Try the same, keeping the numbers as numeric values.

The second problem is that the last load doesn't have any source of the data (from/resident/autogenerate) and therefore it's considered as a Preceding load. I added Autogenerate to your logic.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Dates:
LOAD

          max(ORDERS.SO_DTL_CRT_DT) as MaxDate,
     min(ORDERS.SO_DTL_CRT_DT) as MinDate
Resident ORDERS;

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

DROP Table Dates;

TIMEDIM:
LOAD

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

Autogenerate 1
while $(vMinDate)+ (IterNo()-1)<= $(vMaxDate)

;

6 Replies
Employee
Employee

Re: Create dates in script

i suggest stepping through your code with the debugger to figure out if there is any loop happening. One guess is just that the formats are not compatible because one side is using an addition and the other is not .


Ex:

TIMEDIM:
LOAD $(vMinDate) + (IterNo()-1) as Date
while  date($(vMinDate)+ (IterNo()-1))<= date($(vMaxDate));


Here is also an alternative code:


TIMEDIM:
LOAD  date( $(vMinDate) + (IterNo()-1)) as Date
autogenerate   $(vMaxDate)-$(vMinDate)+1 ;

b_garside
Valued Contributor

Re: Create dates in script

Check this thread out. It has a video as well.

https://community.qlik.com/thread/48693

MVP & Luminary
MVP & Luminary

Re: Create dates in script

The basic issue that I can see is formatting your min and max values as dates - the date values don't work as well in the following logic. Try the same, keeping the numbers as numeric values.

The second problem is that the last load doesn't have any source of the data (from/resident/autogenerate) and therefore it's considered as a Preceding load. I added Autogenerate to your logic.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Dates:
LOAD

          max(ORDERS.SO_DTL_CRT_DT) as MaxDate,
     min(ORDERS.SO_DTL_CRT_DT) as MinDate
Resident ORDERS;

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

DROP Table Dates;

TIMEDIM:
LOAD

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

Autogenerate 1
while $(vMinDate)+ (IterNo()-1)<= $(vMaxDate)

;

oscarmqz
Contributor III

Re: Create dates in script

Tried both, none worked

When debugging the values for vMinDate and vMaxDate are correct, but nothing happen, when clicking on "Step" it simply ends running

oscarmqz
Contributor III

Re: Create dates in script

Worked like a charm, thanks Oleg.

Employee
Employee

Re: Create dates in script

Your right .. forgot to replace iterno() with rowno()   (for the 2nd example)

TIMEDIM:
LOAD  date( $(vMinDate) + (rowno()-1)) as Date
autogenerate   $(vMaxDate)-$(vMinDate)+1 ;