sorry but I don't understand your question.
Could you add an image of your schema tables? You do Left Join (Orders) but where is the code for Orders table?
Hint, when you concatenate data from different LOAD is a best practic to use CONCATENATE (DateLink).
Why do you create a table Key? If you use Left Join (Orders) you not need "Key:"
Luca Jonathan Panetta
I guess I should start from the beginning - I am trying to create a Common Calendar, but in order to do so you need the same Fact Key connecting each piece of the data link.
However, I have a situation where I do not have the same Fact Keys. See below:
Date Fact Key
What I was trying to do is create a common "Key" using the AutoNumberHash function, in order to combine all the Fact Keys - then use that to create my common calendar. However, it is not working - and I'm not sure why.
Can anyone help me with simply, start to finish, creating a Common Calendar with Different Fact Keys?
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
QlikCommunity_Thread_191810.qvw 190.2 K
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.
I apologize, I thought I included this as well - each Order is made up of OrderItems. See below.
OrderID, OrderDate, Revenue
123, 7/1/2015, 100.00
456, 7/15/2015, 100.00
789, 8/1/2015, 100.00
LOAD * INLINE [
you need to create a custom Key for each table, I have add LOAD before LOAD * inline [...];
LOAD OrderID & '_' & OrderItemID & '_' & ConcessionID as Key // ,ConcessionID ,ConcessionAmount // ,OrderID // ,OrderItemID ,ConcessionDate ; LOAD * 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 ];
In attach my solution for you.
Luca Jonathan Panetta
Customer Service Mock-Up.qvw 168.5 K