Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Common Calendar with Different Fact Keys

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

Fact KeyDateType
OrderIDOrderDate
OrderIDShipDate
ConcessionIDConcessionDate
CustomerServiceTicketIDTicketCreatedDate


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

Message was edited by: Andii Toole

1 Solution

Accepted Solutions

Re: Common Calendar with Different Fact Keys

maybe one solution could be also:

QlikCommunity_Thread_191810_Pic2.JPG

QlikCommunity_Thread_191810_Pic3.JPG

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

14 Replies
pljsoftware
Contributor III

Re: Common Calendar with Different Fact Keys

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

mark6505
Valued Contributor III

Re: Common Calendar with Different Fact Keys

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

vincent_ardiet
Contributor III

Re: Common Calendar with Different Fact Keys

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.

manojkulkarni
Valued Contributor II

Re: Common Calendar with Different Fact Keys

Not applicable

Re: Common Calendar with Different Fact Keys

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?

MVP
MVP

Re: Common Calendar with Different Fact Keys

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.

Not applicable

Re: Common Calendar with Different Fact Keys

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

Hi,

maybe helpful as well:

Fact Table with Mixed Granularity

Generic keys

regards

Marco

pljsoftware
Contributor III

Re: Common Calendar with Different Fact Keys

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

Community Browser