Common Calendar with Different Fact Keys

[#1]

How do I create a Common Calendar with Different Fact Keys? (This post has been edited based on feedback in the below comments)

Fact Key	DateType
OrderID	OrderDate
OrderID	ShipDate
ConcessionID	ConcessionDate
CustomerServiceTicketID	TicketCreatedDate

Attached is an example of the tables I am working with - and the different fact keys they use.

Message was edited by: Andii Toole

[MarcoWedel · Accepted solution]

maybe one solution could be also:

Orders:  
LOAD *  
INLINE [  
    OrderID, OrderDate, Revenue  
    123,  7/1/2015, 100.00  
    456, 7/15/2015, 100.00  
    789,  8/1/2015, 100.00  
];  

CustomerServiceConcessions:  
LOAD ConcessionID&'_'&OrderID as ConcessionOrderID,  
    *  
INLINE [  
    ConcessionID, ConcessionAmount, OrderID, OrderItemID, ConcessionDate  
    10,  5.00, 123,  ,  9/1/2015  
    15,  5.00,    , 5,  8/1/2015  
    16, 50.00,    , 6, 8/10/2015  
];  

CustomerServiceTickets:  
LOAD TicketID&'_'&OrderID as TicketOrderID,  
    *  
INLINE [  
    TicketID, OrderID, TicketReason, TicketCreatedDate  
    99, 789, Cancellation, 8/15/2015  
]; 

DateLink:
LOAD Distinct
     OrderID
    ,OrderDate as Date
    ,'Order' as DateType
RESIDENT Orders;

Join

LOAD Distinct
     ConcessionOrderID,
     ConcessionID
    ,OrderID
    ,ConcessionDate as Date
    ,'Concession' as DateType
RESIDENT CustomerServiceConcessions;

Join

LOAD Distinct
     TicketOrderID
    ,TicketID
    ,OrderID
    ,TicketCreatedDate as Date
    ,'Ticket' as DateType
RESIDENT CustomerServiceTickets;

DROP Fields ConcessionID, OrderID From CustomerServiceConcessions;
DROP Fields TicketID, OrderID From CustomerServiceTickets;

hope this helps

regards

Marco

[#3]

Hi, this is amazing! But in this scenario I want to still be able to tie the concessions made at the OrderItemID level back to their OrderID.

So a concession made on OrderItemID 5, should still connect to OrderID 456.

Right now, the calendar is working - but I can't tie it back to the information I still have at the Order level.

[MarcoWedel]

can you describe how OrderItemID 5 is connected to OrderID 456 in your source data?

thanks

regards

Marco

[#5]

I apologize, I thought I included this as well - each Order is made up of OrderItems. See below.

Orders:

LOAD *

INLINE [

    OrderID, OrderDate, Revenue

    123,  7/1/2015, 100.00

    456, 7/15/2015, 100.00

    789,  8/1/2015, 100.00

];

OrderItems:

LOAD * INLINE [

     OrderID, OrderItemID

     123,1

     123,2

     123,3

     456,4

     456,5

     456,6

     789,7

     789,8

     789,9

];

[#6]

This is the correct answer for my original question - although it doesn't solve all of my problems

Still, it worked and I appreciate the effort!