Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
RobertPatrician
Contributor II
Contributor II

Data load script: Create series of dates?

Greetings, I'm hoping to find some help generating a date dimension for my project that isn't reliant on an external file being uploaded. I want it to start with a date of 1/1/1970 in a field, and generate up through 1/1/2050. 

Once I have that in a table, I can resident load it to do the calculations for things like fiscal quarters, fiscal years, and other information. 

What would I put into the load script to generate such a table?

Labels (1)
1 Solution

Accepted Solutions
joseph_morales
Creator II
Creator II

Hi @RobertPatrician ,

You can use the following code to generate the table. I did it using the Autogenerate and While functions.

LET vMinDate =Num('1/1/1970');
LET vMaxDate =Num('1/1/2050');

Dates:
LOAD DATE($(vMinDate) + IterNo() - 1) AS TempDate
AutoGenerate (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Regards

Best Regards,
Joseph Morales

View solution in original post

2 Replies
joseph_morales
Creator II
Creator II

Hi @RobertPatrician ,

You can use the following code to generate the table. I did it using the Autogenerate and While functions.

LET vMinDate =Num('1/1/1970');
LET vMaxDate =Num('1/1/2050');

Dates:
LOAD DATE($(vMinDate) + IterNo() - 1) AS TempDate
AutoGenerate (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Regards

Best Regards,
Joseph Morales

View solution in original post

RobertPatrician
Contributor II
Contributor II
Author

Awesome, thank you! This will save a lot of awkwardness with excel files for the date dimension.