Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

View solution in original post

14 Replies
Highlighted
Creator III
Creator III

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

Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

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.

Highlighted
Partner
Partner

Highlighted
Not applicable

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?

Highlighted
MVP
MVP

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.

Highlighted
Not applicable

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!

Highlighted

Hi,

maybe helpful as well:

Fact Table with Mixed Granularity

Generic keys

regards

Marco

Highlighted
Creator III
Creator III

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