Master calendar

[dario_frimel]

Hello everyone,

I tried looking up for this question but I couldn't work around it. I have two same data imported for two regions.

Each region consists of same tables that are associated. Each region data has own associations and they are not connected between themselves. Issue is, I have date field in data for both regions and what I am trying to do is make master calendar that would affect all those tables. 

For example, when I choose 2017 year, I want both data to get filtered to that year.  

Thanks in advance for suggestions and solutions provided!

Best regards

[juraj_misina]

Hi,

simply associate those tables with a common date field and build a master calendar (or autogenerated calendar) on top of that field.

Juraj

[dario_frimel]

This is what I've used to link date data among one region,  but now I need to apply this to other region as well and simply creating another master calendar with input fields of other data doesn't link those two. Can it be done like with JOIN so I join min and max date of both tables in one master calendar? 

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(invoices.region1.DATE) as minDate,
max(invoices.region1.DATE) as maxDate
Resident [invoices.region1];

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS DatumMC,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Month(TempDate)&'-'&Year(TempDate) As MjeGod,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[dplr-rn]

Hi Dario

Check the link below gives a better recipe for master calendar.
http://qlikviewcookbook.com/2015/05/better-calendar-scripts/
as long as date field column is same between all the tables it will work and create a common calendar

[dario_frimel]

Thanks for that link, it's very helpful as it made me figure I use older code for master calendar, although I am unclear about same date field column, all dates I have are same in a way that they are in same form, do they need to have same column name or?

[MK9885]

If you have same field/values in both the region tables then why not concatenate it?

Maybe rename those 2 date fields to something similar like RegionDate and create a new field in both the tables with DateID.

Map your single table containing data from 2 tables to DateID. You can still keep original date fields...

Use below calendar it has few flags which would help you in future..

DateID would be the PrimaryKey. where ever the fields are missing give 0

0 as ABC in your tables to do concatenation.

QuarterNAME:

LOAD * Inline [
Quarter , QuarterFullName
Q1 ,FIRST
Q2 ,SECOND
Q3 ,THIRD
Q4 ,FOURTH
];

// to create Quarters ie Q1,Q2

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(invoices.region1.DATE) as minDate,
max(invoices.region1.DATE) as maxDate
Resident [invoices.region1];

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 as Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

trim(date(TempDate,'YYYYMMDD')) as [DateID],
date(TempDate,'MM/DD/YYYY') as [Region Date ],
WeekStart(TempDate ) as [Effective Week],

if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

day(TempDate) as Day,
TempDate as [US Calendar Format],
date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],
date(TempDate,'WWWW') as [Full Day Name],
year(TempDate) as Year,
inyear(TempDate,today(),-1) * -1 as [First PY],
inyear(TempDate,today(),-2) * -1 as [Second PY],
inyeartodate(TempDate,today(),0) * -1 as [CYTD],
inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],
inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],
if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,
if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Ceil(Month(TempDate)/3) as [Quarter Number],
quarterName(TempDate) as [Quarter Name],
yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as [Quarter Year],

inquarter(TempDate,today(),-4) * -1 as [First PQ],

inquarter(TempDate,today(),-8) * -1 as [Second PQ],
inquartertodate(TempDate,today(),0) * -1 as [CQTD],
inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],
inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],
date(monthstart(TempDate),'MM') as [Month Number],
num(month(TempDate)) as Num_Month,
month(TempDate) as Month,
date(monthstart(TempDate),'MMMM') as [Month Full Name],
monthstart(TempDate) as [Calendar Month Start Date],
monthend(TempDate) as [Calendar Month End Date],
date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],
date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],week(TempDate) as Week,
week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],
week(weekstart(TempDate)) & '-' & Month(TempDate) as [Week Month],
weekDay(TempDate) as [Week Day]

Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;
Drop Table QuarterNAME;

[jonathandienst]

I think you may be on a poor model design. Have you considered concatenating the region data (perhaps with created 'Region' field). This means no issues with the calendar and no need for a performance sapping link table.

[dplr-rn]

Agree with @jonathandienst  that should be considered if the tables structures are essentially the same 

[dplr-rn]

Would help to give a little detail on your table structures.

[dario_frimel]

Thanks everyone for suggestions, I have made changes to data model to have concatenated tables. Before concatenating tables I have add Calculated field that specifies region.

But the issue I am facing now is that in data manager tables don't get updated now that they are concatenated, is there a reason to it? It's very important to me as tables have to be updated every hour.