Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Awesome, thank you! This will save a lot of awkwardness with excel files for the date dimension.