topic Re: Master Calendar in QlikView

Master Calendar

Tue, 13 Dec 2011 11:53:50 GMT

I have several QVDs with date fields (Year and Month). In other to avoid a loop, I have decided to use a master calendar but its still not working properly (the dates are not joinning) - e.g. when I select 2010, it does not affect the selection, it duplicates some of the data, etc. Could you please help me as its my first time using Master Calendar.

CalendarLinkage:

LOAD

Year_O as [Calendar Year],

Month_O as [Calendar Month]

From

data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

CONCATENATE (CalendarLinkage)

LOAD

Year_C as [Calendar Year],

Month_C as [Calendar Month]

FROM

data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);

CONCATENATE (CalendarLinkage)

LOAD

Year as [Calendar Year]

from

\\10.193.2.14\qvapps\Live\InlinePurchases_2.xls

(biff, embedded labels, table is Sheet1$);

Thanks in advance

Re: Master Calendar

Tue, 13 Dec 2011 12:15:31 GMT

apply this:

let vStartDate= num(MakeDate(2010));

cal1:

load Date($(vStartDate)+RowNo()-1) as Date

AutoGenerate(num(today())- $(vStartDate)+1);

cal2:

load

year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day,

//Month(Date) &'-'&Year(Date) as Monthyear,

dual((Month(Date) &'-'&Year(Date)),num(MakeDate(year(Date),month(Date)))) as Monthyear

Resident cal1;

drop table cal1;

note: instead of 2010 you can replace the year which is your starting year.

By applying the above script your master calender gets ready now you can associate the dates in the table by aliasing as:

calender linkage:

load *,

date as Date

from qvdname;

note: here date is the field which are the dates coming from your data.

you get the table view as:

check PFA

hope this makes you clear.

regards

vijit

Re: Master Calendar

Tue, 13 Dec 2011 12:40:13 GMT

Hi Vijit

Thanks for the below. its helpful. I am probably a step closer now...

Please see attached.

Basically, The data is still duplicating and selection not effective when I select "Calendar Year". Please help

Thanks a lot

Re: Master Calendar

Wed, 14 Dec 2011 04:55:17 GMT

As shown in your Doc1 there is no linking between master calender and calender linkage, so do aliasing to associate

calender linkage table with cal2 as:

calender linkage:

LOAD

Year_O as Year,

Month_O as [Calendar Month]

From

data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

try this , tell me if the problem persist.

Re: Master Calendar

Wed, 14 Dec 2011 08:45:55 GMT

Hi There

That seem to work but when I add (Data\SAPMM\Projects\COVP.qvd(Qvd)) - see last statement, I get loop problem "One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

I have other tables to add with dates... do you know the best way to loose them? The relationship is in the diagram I sent to you earlier.

Thanks so much for your help

CalendarLinkage_Sales:

LOAD

%InvoiceBillingDocument_Key,

Year_O as Year,

Month_O as Month

From

data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

CONCATENATE (CalendarLinkage_Sales)

LOAD

%InvoiceBillingDocument_Key,

Year_C as Year,

Month_C as Month

FROM

data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);

CONCATENATE (CalendarLinkage_Sales)

LOAD

Year as Year

from

10.193.2.14\qvapps\Live\InlinePurchases_2.xls

(biff, embedded labels, table is Sheet1$);

CONCATENATE (CalendarLinkage_Sales)

LOAD

Year_Pur as ,

Month_Pur as

From

Data\SAPMM\Projects\COVP.qvd(Qvd);

Re: Master Calendar

Wed, 14 Dec 2011 09:12:19 GMT

aliasing is the only way to avoid loosely coupled tables, there is a circular reference because of that qlikview create loosely coupled tables.

check your data modelling and rename the fields which causes circular reference.

Do one thing attach your original qvw file so that I can check the data modelling.

Re: Master Calendar

rohit214 — Wed, 14 Dec 2011 09:14:03 GMT

hi try this

Calendar:

LET vDateMin = Num(MakeDate(2009,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

Resident TempCalender Order By TempDate ASC;

Re: Master Calendar

Wed, 14 Dec 2011 09:32:46 GMT

Do you have a personal email address?

Re: Master Calendar

Wed, 14 Dec 2011 10:22:22 GMT

send it to:

vijit_jindal@yahoo.com

Re: Master Calendar

Wed, 14 Dec 2011 12:05:05 GMT

After checking your data modelling I think problem is of synthetic table as there are more than one field common in

Timesheetorders & forecast .

Cost centre

Cost rates

year

key

So rename the fields of forecast table something else except cost centre as:

forecast:

load

applymap ('CostRateMap',[Cost Centre] & [Year],'unknown') & '\' & Year & '\' & [Cost Centre] & '\' & [User_Name] & '\' & [Task Code] as Key1,

applymap ('CostRateMap',[Cost Centre] & [Year],'unknown') as CostRates1,

[Year] as year1

from qvdname;

And then apply master calender.

note:

link only one field of master calender with the calender linkage otherwise again synthetic table will form.

regards

vijit

Re: Master Calendar

Wed, 14 Dec 2011 12:38:25 GMT

Thanks for the sync table help.

Do i need to include the primary key for each qvd?

let vStartDate= num(MakeDate(2010));

cal1:

load Date($(vStartDate)+RowNo()-1) as Date

AutoGenerate(num(today())- $(vStartDate)+1);

cal2:

load

year(Date) as ,

Month(Date) as Calendar Month],

Day(Date) as Day,

dual((Month(Date) &'-'&Year(Date)),num(MakeDate(year(Date),month(Date)))) as Monthyear

Resident cal1;

drop table cal1;

CalendarLinkage_Sales:

LOAD

%InvoiceBillingDocument_Key,

Year_O as ,

Month_O as

From

data\SAPMM\Projects\BSID_OpenItems_Header.qvd (qvd);

CONCATENATE (CalendarLinkage_Sales)

LOAD

%InvoiceBillingDocument_Key,

Year_C as ,

Month_C as

FROM

data\SAPMM\Projects\BSAD_ClosedItems_Header.qvd (qvd);