Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mmaidana
New Contributor

Master Calendar

Hi,

I have two tables (Qlik Sense 3.0), with dates, one with historical dates and one with forecast dates.  They both start and end at different points in time.  My query is... (apologies for my ignorance, this is my first go at this) can the one master calendar look at a min date from the historical data and max date from the forecast data?  Or do I need two different calendars or some other solution?

Thanks in advance.

Monica

1 Solution

Accepted Solutions

Re: Master Calendar

No, like I said first load the two main tables so all date values from those two tables exist in the in-memory database. Rename the date fields if necessary so there's only one date field name in the final data model. Then create the master calendar.


talk is cheap, supply exceeds demand
4 Replies

Re: Master Calendar

Yes, that's possible. Best to give the date field in both tables the same name so they will both be linked to the master calendar. After loading both tables you can generate a master calendar using the date field that now contains all the historical and forecast dates. Something like this:

Temp:

LOAD

     Date(min(FieldValue('MyDate', RecNo())) as MinDate,

     Date(max(FieldValue('MyDate', RecNo())) as MaxDate,

AUTOGENERATE FieldValueCount('MyDate') ;

Let vMinDate = peek('MinDate');

Let vMaxDate = peek('MaxDate');

DROP TABLE Temp;

MasterCalendar:

LOAD

     MyDate,

     Year(Date) as Year,

     Month(Date) as Month,

     ...etc...

LOAD

     Date($(vMinDate) + RecNo() -1) as MyDate

AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1 ;


talk is cheap, supply exceeds demand
Not applicable

Re: Master Calendar

Just follow procedure like this

OrdersCalTemp:

Load Min(BILLDATE) as MinOrdDate,

Max(BILLDATE) as MaxOrdDate

Resident FACTTABLE;

let EndDate=Date(Peek('MaxOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

let StartDate=Date(Peek('MinOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

let caldays=(EndDate-StartDate)+1;

let vToday=num(EndDate);

Cal:

Load RecNo() as RecordNo,

if(RecNo()=1,Date('$(StartDate)'),Date(peek("D")+1)) as D

autogenerate(caldays);

Calendar:

BILLDATE,

........

.......

Resident Cal;

mmaidana
New Contributor

Re: Master Calendar

Hi,

Thank you for your reply.  So, do I mention the two tables in the master calendar script? eg Do I need to load two different temp tables to load the two date fields from the two tables?

Re: Master Calendar

No, like I said first load the two main tables so all date values from those two tables exist in the in-memory database. Rename the date fields if necessary so there's only one date field name in the final data model. Then create the master calendar.


talk is cheap, supply exceeds demand
Community Browser