Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

Limit record in master calendar table

I used below scripts to generate a date dimension table.  however, the latest records is 3/24/2020 and I expected the table should store records up to 202003.  But it now store up to 202012.  How can I fix it?

DROP Table Temp;

MasterCalendar:
Load *,
AutoNumber(Period, 'PeriodID') as [PeriodID],
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID]
;
LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;
LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

 

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Limit record in master calendar table

Check $(vMaxDate) because this variable determined until which date the calendar-script is running.

- Marcus

Highlighted
Contributor

Re: Limit record in master calendar table

can i simply convert the vMaxDate into a number 'YYYYMM' e.g. 202003?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Limit record in master calendar table

An appropriate formatting + converting is surely possible but I use in such cases simply: year(date) * 100 + month(date)

- Marcus

Highlighted
Contributor

Re: Limit record in master calendar table

Found the root cause. My bad, the data files passed from my colleagues are in different date format. 

I changed it as below and the original scripts are working fine.

SET DateFormat='M/D/YYYY';