Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
Am having Year(2016) , Month(Jan,Feb,Mar & So on..) in My table. Need to connect my master Calendar, How can i connect this.
Can any provide me the Script..
Regards,
Helen
Do you have date in your data?
For now create the Field in your data and master calendar as below and link on that field
=date(date#(Year&Month,'YYYYMMM'),'MMM-YYYY') as Link
If you have Year and Month field in your data , then you need to make date range from that in load script,
using YearStart and YearEnd
Hi Helen,
If you have multiple sub date fields in your FACTS table, you better concat them as a single date format to avoid Syntetic keys with your master calendar.
So first concat them in Facts table to have only one date KEY (full date using first day of each month or just yearmonth format if enough for you)
Then have the same date KEY generated in your Mastercalendar which could also have sub dates fields for analysis (Year, Month....):
MasterCalendar:
LOAD
date(date#(Year&Month, 'YYYYMMM'), 'MMM-YYYY') AS MonthYear,
...
...
Best,
Maurice
Hi,
I hope your Data is like this,
Data:
LOAD *,
Date(Date#('01'&'/'& Month & '/'&Year,'DD/MMM/YYYY'),'DD/MM/YYYY') as Date
INLINE [
Month, Year, Data, Customer
Jan, 2016, 423, A
Feb, 2016, 233, B
Mar, 2016, 33, C
Apr, 2016, 23, D
May, 2016, 333, E
Jun, 2016, 332, F
Jul, 2016, 33, G
Aug, 2016, 54, H
Sep, 2016, 544, I
Oct, 2016, 4542, J
Nov, 2016, 433, K
Dec, 2016, 22, L
];
Then by using Calendar Script ,Will have the output like this,
Hope this helps ,
PFA,
Hirish