topic Help on Calender! in QlikView

Help on Calender!

Thu, 11 Mar 2010 05:39:08 GMT

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

Help on Calender!

Miguel_Angel_Baeyens — Thu, 11 Mar 2010 08:45:00 GMT

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.

Help on Calender!

blaise — Thu, 11 Mar 2010 10:50:20 GMT

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).

Help on Calender!

Thu, 11 Mar 2010 11:18:52 GMT

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

Help on Calender!

Thu, 11 Mar 2010 12:44:59 GMT

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

Help on Calender!

Thu, 11 Mar 2010 12:48:18 GMT

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

Help on Calender!

Thu, 11 Mar 2010 12:52:59 GMT

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

Help on Calender!

blaise — Thu, 11 Mar 2010 13:22:00 GMT

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;

Help on Calender!

Fri, 12 Mar 2010 05:29:15 GMT

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

Help on Calender!

Anonymous — Fri, 12 Mar 2010 10:15:48 GMT

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

Help on Calender!

blaise — Fri, 12 Mar 2010 12:14:27 GMT

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"...

Help on Calender!

Sat, 13 Mar 2010 07:40:04 GMT

Hi AT!

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]

Thanks and Regards,

Rikab