Master Calendar

[#1]

If I have a "Sales Date" and a "Goal Date" is there anyway to have these dates combined into one master table even if they come from different source data? I was able to create a master calendar for Sale & Goal, but need to combine thses some how. Any help would be greatly appreciated.

[its_anandrjs]

Yes but you need to create same name field in both the tables and connect it with the master calendar.

As you say you have already Master calendar based on the Sales and Goal table then you have to use your table like below script

Eg:-

SalesTable:

Load

"Sales Date"

"Sales Date" as CommonDate

From Location;

GoalTable:

Load

"Goal Date"

"Goal Date" as CommonDate

From Location;

//Master Calendar based on the Sales and Goal Table and which is based on the Sales and Goal dates

MasterCalendar:

Load

CommonDate

From Location;

[MK_QSL]

Try below in your script...

=====================

Orders:

Load

  OrderNumber,

  ConfirmationDate,

  DeliveryDate,

  Value

Inline

[

  OrderNumber, ConfirmationDate, DeliveryDate, Value

  760864, 06/01/2014, 16/01/2014, 200

  760865, 06/01/2014, 16/01/2014, 120

  760866, 06/01/2014, 20/02/2014, 300

  760867, 06/01/2014, 20/02/2014, 400

  760868, 06/01/2014, 25/03/2014, 250

  760869, 06/01/2014, 27/03/2014, 180

  760870, 01/02/2014, 12/04/2014, 600

  760871, 06/02/2014, 12/04/2014, 300

  760872, 06/02/2014, 06/06/2014, 200

  760873, 16/03/2014, 06/05/2014, 180

  760874, 16/03/2014, 06/06/2014, 150

  760875, 16/03/2014, 06/06/2014, 250

  760876, 26/03/2014, 06/06/2014, 210

  760877, 26/04/2014, 15/05/2014, 230

  760878, 26/04/2014, 25/05/2014, 400

  760879, 16/05/2014, 30/05/2014, 230

  760880, 16/05/2014, 01/06/2014, 90

  760881, 16/06/2014, 18/06/2014, 100

  760882, 16/06/2014, 26/06/2014, 120

];

OrderConfirm:

Mapping Load

  OrderNumber,

  ConfirmationDate

Resident Orders;

OrderDelivery:

Mapping Load

  OrderNumber,

  DeliveryDate

Resident Orders;

NoConcatenate

Final:

Load

  OrderNumber,

  ApplyMap('OrderConfirm',OrderNumber,Null()) as Date,

  'Order' as DataType,

  1 as CountFlag,

  Value

Resident Orders;

Load

  OrderNumber,

  ApplyMap('OrderDelivery',OrderNumber,Null()) as Date,

  'Delivery' as DataType,

  -1 as CountFlag,

  Value

Resident Orders;

//Canonical Date Calendar

MinMaxDate:

Load

  Date(Min(Date)) as MinDate,

  Date(Max(Date)) as MaxDate

Resident Final;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MaxMaxDate'));

TempCalendar:

Load

  Date($(vMinDate) + RowNo() - 1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

MasterCalendar:

Load

  TempDate as Date,

  Month(TempDate) as Month,

  Year(TempDate) as Year

Resident TempCalendar

Order By TempDate Asc;

Drop Tables Orders, MinMaxDate, TempCalendar;

[SunilChauhan]

Eg:-

Load

[Sales Date]

[Sales Date] as CommonDate

From yourDBPath;

Load

[Goal Date]

[Goal Date] as CommonDate

From yourDBPath;

MasterCalendar:

Load

[Sales Date] as CommonDate

From yourDBPath;

Concatenate(MasterCalendar) ////this is important step

Load

[Goal Date] as CommonDate

From yourDBPath;

hope this helps