Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I have following table....
work_id name startdate enddate
1 a 11/01/2014 12/01/2014
2 b 11/01/2014 12/01/2014
2 c 12/01/2014 13/01/2014
1 c 11/01/2014 12/01/2014
3 b 13/01/2014 14/01/2014
4 c 14/01/2014 15/01/2014
like consider, i have some 1000 of records, now how do i create a master calendar for these start and end date, are there any other way to do this.?
Thanks..
Hi,
Calendar always depend on on what basis you want to do analysis your data
1: If you want to do analysis on Start Date then create calendar on Start_Date
2: If you want to do analysis on End Date then create calendar on End_Date
3: If you want to do analysis on Start Date as well as End Date then create master calendar by taking min of start_Date
and max of End date
Regards
Do you want to link your existing table with the master calendar ??
try like this
TEMP:
LOAD
work_id&'-'&name as Key
work_id,
name,
startdate,
enddate
from table;
Link:
LOAD Key,
startdate as date_key
resident
TEMP;
LOAD
Key,
enddate as date_key
resident
TEMP;
Master_Calendar:
LOAD Date as date_key,
......
if i am taking min as Startdate and max as Enddate. how would i link to the existing table.?
can you explain how can i link the master calendar with that existing table.?
Hello Kishore,
You can use startdate as your date variable to generate others date fields like Quarter, Month, Week
Find the attached file....
Hope you find this helpful....
thank you.. i have already done with the start date.. i want to know, do i have to create a master calendar with multiple dates.!
In order to link multiple dates with the calendar I have created a link table where I am merging all the dates into one single date column and then finally linking the link table with the calendar table . See my last post of the sample code
I am not clear with your sample code.. can you explain me more on that.!
I have two tables. namely
workflow table
task table
workflow table fields.
workflow_id workflow_name start_date end_date
1 a 10/02/2014 12/02/2014
task table fields.
workflow_id task_id start_date end_date
1 1a 10/02/2014 10/02/2014
1 1b 10/02/2014 11/02/2014
Here.. each workflow_id has more than one task_id and start and end date...
I want to create a pivot table. where i have to include all these..
I want to create either master cal / canonical date.. To make selections according to d dates it can be either from workflow table or from task table
Thanks..
Thanks.
Can you share the KPIs you are trying to create. As they drive the decisions whether you need multiple calendars or canonical calendar or calendar based on single date, you may create canonical date from all the available dates, check HICs blog on this.