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?
What happens with the solution i has provided? If you don't tell us, is very difficult...
The script i sent make the same result you sent...
Im confused... what do you need?
I didn't get your script working. Do you have a qvw example including the script?
I've attached your qvw with my suggestions.
Reload it.
If it gives you an error, send the error or send the file to I check the reload.
Hello Masha,
I like your solution but I do see an issue with it.
How do you handle a date being both valid for Change Date and Stock Date? What type is it going to be? Don't you run the risk of excluding dates?
Thanks
Benoit,
If a date is both Change Date and Stock Date, it is going to have both types.
The Calendar table will look like the following:
CalendarKey DateType Month
'Nov2012/Nov2012' 'Change Date' 'Nov 2012'
'Nov2012/Nov2012' 'Stock Date' 'Nov 2012'
i think this is u want,ple let me know friend
date1:
LOAD date(date#(ChangeDateID,'YYYYMMDD'),'DD/MM/YYYY') as ChangeDateID,
(MonthID) as MonthID ,
date(date#(StockDateID,'YYYYMMDD'),'DD/MM/YYYY') as StockDateID
FROM
(ooxml, embedded labels, table is TB02_20120823_120900);
quarter_map:
mapping LOAD RowNo() as month,
'Q' & Ceil(RowNo()/3) as Quarter
AutoGenerate (12);
temp:
LOAD max(StockDateID) as max,
min(StockDateID) as min
Resident date1;
let mindate=num(Peek('min',0,'temp'));
let maxdate=num(Peek('max',0,'temp'));
DROP Table temp;
tep:
LOAD $(mindate)+IterNo()-1 as num,
date($(mindate)+IterNo()-1) as date
AutoGenerate 1 While $(mindate)+IterNo()-1<=$(maxdate);
QUALIFY *;
UNQUALIFY StockDateID;
mastercalender:
LOAD date as StockDateID,
Year(date) as Year,
ApplyMap('quarter_map',month(date),null()) as Quarter
Resident tep
Order by date;
UNQUALIFY *;
QUALIFY *;
UNQUALIFY ChangeDateID;
mastercalender:
LOAD date as ChangeDateID,
Year(date) as Year,
ApplyMap('quarter_map',month(date),null()) as Quarter
Resident tep
Order by date;
DROP Table tep;
UNQUALIFY *;
I see; that makes sense. I missed the use of Concatenate here.
Thanks