I want to create generalized calender table from where all the date fields can be accessed. Below i have given the script which i am using to load the sales data which contains the date field also. I want to create the generalized calender table using the date field which is available in the sales data.
I prefer something like the below one. But i don't want synthetic keys to be created. Any suggestions please!
Calendar: Load *, Day(DATE) as Day, DayName(DATE) as [Day Name], WeekDay(DATE) as [Day of Week], WeekName(DATE) as [Week Name], LunarWeekName(DATE) as [Lunar Week Name], Month(DATE) as [Month], MonthName(DATE) as [Month Name], QuarterName(DATE) as [Quarter Name], Year(DATE) as [Year] ;
// Sales data script Sales: ODBC CONNECT TO [1]; SQL SELECT AREA, BRAND, `COMP_`, DATE, FORMU as FORMULATION, month(DATE) AS MONTH, YEAR(FYEAR) AS YEAR, `HQ_DESC`, ORG, PACKING, `PRD_CD`, `PRD_DESC`, REGION, SBU, SQTY, SVAL, TQTY, TVAL, TYPE, ZONE FROM `E:\QlikView\Application\Data`\new3;
The big disadvantage with not using the autogenerate function is longer load time. By doing a load distinct Date resident Sales, and after that a resident load again you end up with a master calendar that takes a lot more time to create than the autogenerate.
The only disadvantage i see with using a autogenerated master calendar is that you probably add dates with no relevant "sales" data. But this can be solved doing a left keep or where exists when loading the autogenerated master calendar. Also this small disadvantage can turn into an advantage when you dont need to answer questions from the users like "what the heck has happened to January the 3rd in 2009"...
Thanks your effort. I think i have got the result which i am looking for.In this i want to get the financial year also let me know how can i get it.
Calender: Load DATE, Day(DATE) as Day, DayName(DATE) as [Day Name], WeekDay(DATE) as [Day of Week], WeekName(DATE) as [Week Name], LunarWeekName(DATE) as [Lunar Week Name], Month(DATE) as [Month], MonthName(DATE) as [Month Name], QuarterName(DATE) as [Quarter Name], Year(DATE) as [Year]
