topic Re: Common Calendar with Different Fact Keys in QlikView

Common Calendar with Different Fact Keys

Tue, 17 Nov 2015 20:04:16 GMT

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

Re: Common Calendar with Different Fact Keys

pljsoftware — Wed, 18 Nov 2015 12:48:10 GMT

Hi Andii,

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:"

Regards

Luca Jonathan Panetta

Re: Common Calendar with Different Fact Keys

Mark_Little — Wed, 18 Nov 2015 12:58:28 GMT

Hi,

It isn't a real answer, but it is certainly worth a read and considering, this is how i would normally approach multiple dates for calendars.

Canonical Date

Mark

Re: Common Calendar with Different Fact Keys

vincent_ardiet — Wed, 18 Nov 2015 13:11:01 GMT

I don't understand what is your problem.

[Order] contains the [Key] field so it is joined to [DateLink].

[DateLink] contains the [Date] field and [CommonCalendar] also.

So everything is connected.

Re: Common Calendar with Different Fact Keys

manojkulkarni — Wed, 18 Nov 2015 13:27:18 GMT

Refer below link

Canonical Date

The Master Calendar

Re: Common Calendar with Different Fact Keys

Wed, 18 Nov 2015 18:09:55 GMT

Hi everyone,

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

OrderDate OrderID

ShipDate OrderItemID

ConcessionDate ConcessionID

TicketCreatedDate OrderID

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?

Re: Common Calendar with Different Fact Keys

swuehl — Wed, 18 Nov 2015 18:21:34 GMT

Can you elaborate how your fact table structure looks like?

Best by posting some sample (potentially mock up) data records, or even better a sample QVW that can be reloaded.

Re: Common Calendar with Different Fact Keys

Wed, 18 Nov 2015 21:41:31 GMT

Hi, good idea! I re-wrote my original question and attached a mock-up of the kind of information I am using, along with a couple comments (in the script) to show what I'm looking at and where I am encountering problems. Any and all suggestions are welcome!

Re: Common Calendar with Different Fact Keys

MarcoWedel — Thu, 19 Nov 2015 00:22:08 GMT

Hi,

maybe helpful as well:

Fact Table with Mixed Granularity

Generic keys

regards

Marco

Re: Common Calendar with Different Fact Keys

pljsoftware — Thu, 19 Nov 2015 14:55:43 GMT

Hi Andii,

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.

Best Regards

Luca Jonathan Panetta

Re: Common Calendar with Different Fact Keys

MarcoWedel — Thu, 19 Nov 2015 22:52:09 GMT

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

Re: Common Calendar with Different Fact Keys

Thu, 19 Nov 2015 23:07:25 GMT

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.

Re: Common Calendar with Different Fact Keys

MarcoWedel — Fri, 20 Nov 2015 00:06:49 GMT

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

thanks

regards

Marco

Re: Common Calendar with Different Fact Keys

Fri, 20 Nov 2015 00:12:49 GMT

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

];

Re: Common Calendar with Different Fact Keys

Fri, 20 Nov 2015 22:14:54 GMT

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!