Help on Calender!

[#1]

Hi All,

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;

Thanks and Regards,

Rikab

[Miguel_Angel_Baeyens]

Hello Rikab,

The key field for what you are trying to achieve is "DATE", so create that field in Master Calendar, and prefix all Calendar fields with "Calendar", for example, so your main table "Sales" will be linked to the calendar through the "DATE" field. Then, you only have to use Calendar fields as dimensions or in expressions to get the values you want to display.

[blaise]

No need to make month(DATE) and year(FYEAR) in your sales table if you use a master calendar. This is the whole point in using a master calendar (ie not making your month() etc in your master data).

[#4]

Why not create your master calendar in an xls with all the additional fields (quarters etc)?

Apart from being a true 'master' it saves having the code in every document and so reduces load, and also means you can use just those elements the individual document needs / add new ones more conveniently.

Regards,

Gordon

[#5]

Hi Blaise!

Let me know how it can be used! I don't have the master calender in my script. That's the reason i have posted it here. Let me know what how can i create the same.

Thanks and Regards,

Rikab

[#6]

Hi Gordan!

Thanks for your suggestion! Can you create the xls sheet once which can link with the sales table without creating the synthetic keys.

Waiting for your reply!

Thanks and Regards,

Rikab

[#7]

Hi Miguel!

I am not getting your point. Can you please do the required changes in my script. Will be thankful to you!

Thanks in advance!

Regards,

Rikab

[blaise]

The following code creates a calendar for this year. Adjust theStartDate and EndDate variables to match your application. There a quite a few ways to make a calendar table, so see this just as an example.

 Let vDateStart = num(date(yearstart(today()),'YYYY-MM-DD'));
Let vDateStop = num(date(yearend(today()),'YYYY-MM-DD'));
Dates:
LOAD
 Date($(vDateStart)+(Iterno()-1),'YYYY-MM-DD') as Date
autogenerate 1
 While Date($(vDateStart)+(Iterno()-1)) <=Date($(vDateStop));

Calendar:
LOAD
 date(Date,'YYYY-MM-DD') as Date,
 year(Date) as Year,
 month(Date) as Month,
 week(Date) as Week,
 day(Date) as Day,
 weekday(Date) as Weekday,
 num(weekday(Date)) as WeekdayNum,
 right(year(Date),2) &'-'& month(Date) as YearMonth
RESIDENT Dates;
DROP TABLE Dates;

[#9]

Hi Blaise!

Thank you! I will try you method and let you know the outcome.

Anybody else having any other method. If yes please post it here.

Thanks and Regards,

Rikab

[Anonymous]

Hi, dsjain,

It is also possible not to create Master Calendar using Autogenerate. You can use already existing dates in your Sales table.

t1:
Load Distinct DATE
Resident Sales;

Calendar:
 DATE,
 Year(DATE) as Year,
 Month(DATE) as Month,
 Week(DATE) as Week,
 ........
Resident t1;

Drop Table t1;

Miguel and blaise are right by saing, that in each table, linked to Master Calendar you need only one key field. mainly this field is Date.
By doing so, you can add several tables to Calendar with one Date field.

In case with several tables you need to be sure, that master calendar contains all unique dates from all connedted tables. Then you will need several Distinct loads:

t1:
Load Distinct DATE
Resident Sales;

Concatenate (t1) Load Distinct DATE
Resident Table2;

Concatenate (t1) Load Distinct DATE
Resident Table3;

........

t2:
Noconcatenate Load Distinct DATE
Resident t1;

Drop Table t1;

You can also use another DATE field in master calendar (e.g. DATE2) if there is a need to separate those dates.

That's the way I do it
Good luck!

Rgs,
AT