Master Calendar

[#1]

Hi

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

[#2]

hi

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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.

[rohit214]

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;

[#8]

Do you have a personal email address?

[#9]

send it to:

vijit_jindal@yahoo.com

[#10 · Accepted solution]

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