Problem with master calendar

[Anonymous]

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..

[PrashantSangle]

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

[avinashelite]

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,

......

[Anonymous]

if i am taking min as Startdate and max as Enddate. how would i link to the existing table.?

[Anonymous]

can you explain how can i link the master calendar with that existing table.?

[akpofureenughwu]

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....

[Anonymous]

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.!

[avinashelite]

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

[Anonymous]

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.

[Digvijay_Singh]

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.