Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody.
I have one fact table with two datefields, ChangeDate and StockDate, and a master calendar. Currently I'm using one of the datefileds to link to the master calendar, Left(ChangeDateID, 6) as MonthID, and it works fine to use a calendar in my application and choose between different dates.
But, I would like to have two calenders in my applications, one with ChangeDate and another one with StockDate.
Should I create two calendars? Is there anyone that have any example to share?
Hi Try this
Data:
Load * Inline
[
StockDate,ChangeDate
08/20/2012,08/21/2012
08/10/2012,08/11/2012
08/15/2012,08/14/2012
07/14/2012,07/21/2012
08/13/2012,08/19/2012
08/12/2012,08/18/2012
08/11/2012,08/17/2012
08/10/2012,08/16/2012
];
CommonDate:
LOAD StockDate as DAte Resident Data;
Concatenate
LOAD ChangeDate as DAte Resident Data;
Hi,
I think you want to have a single calendar which should reflect the data for the other two dates aswell.
So, you can create a link table between your fact table and the master calendar.
This link table will inclued both the dates concatenated or joned as a single name filed called Date which will get link to master calendar.Also you need to create a synthetic key with change date and stock date which will link the fact table and link table.
Hi,
Add a field to your fact table:
MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey
Then create a new master calendar:
Calendar:
LOAD
MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,
'Change Date' as DateType,
MonthName(ChangeDate) as Month
Resident FactTable;
Concatenate (Calendar)
LOAD
LOAD
MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,
Stock Date' as DateType,
MonthName(StockDate) as Month
Resident FactTable;
You can create a list box for DateType field so the user can select which date he is using.
/Masha
Do you have any example on have you suggest to solve the issue?
I've attached a example on my issue.
Hi,
Please find the attached application.Reload it at ur end as i was not having the excel file required to reload.
Hi,
Note that you are linking about months, and leave out days.
If you want to have two different calendars, and link by day, the script could be:
Contract:
LOAD ChangeDateID,
StockDateID
FROM
(ooxml, embedded labels, table is TB02_20120823_120900);
Stock_Calendar:
NoConcatenate
Load Distinct
StockDateID,
MakeDate(Left(StockDateID,4), num(Mid(StockDateID,5,2)),Right(StockDateID,2)) as ST_Date
Resident Contract;
Left Join
Load
ST_Date,
Month(ST_Date) as ST_Month,
Year(ST_Date) as ST_Year,
Day(ST_Date) as ST_Year,
Ceil(Month(ST_Date)/4) as ST_Quarter
Resident Stock_Calendar;
Change_Calendar:
Load Distinct
ChangeDateID,
MakeDate(Left(ChangeDateID,4), num(Mid(ChangeDateID,5,2)),Right(ChangeDateID,2)) as CH_Date
Resident Contract;
Left Join
Load
CH_Date,
Month(CH_Date) as CH_Month,
Year(CH_Date) as CH_Year,
Day(CH_Date) as CH_Year,
Ceil(Month(CH_Date)/4) as CH_Quarter
Resident Stock_Calendar;
Now, you will get the different fields for each date (Stock and Change)
Note that when you will select one date, the other dates will be filter to the registers with the date you have selected.
It still doesn't working successfully. And how do I manage to use two calendar objects with different fields?
See attached files.
The functionallity I'm looking for is like attached qvw. But this is maybe not the best way to do it.