Regarding shared calendar with different keys

[#1]

Hello Friends,

Kindly share your opinion regarding my scenario with shared calendar.

I have 6 fact tables.

fact1  connected  through key k1 to fact2

fact2 connected  through key k2  to fact3

fact3 connected  through key k3 to fact4

fact4 connected  through key k4 to fact5

fact5 connected  through key k5 to fact6

I did some sort of joins between all the facts and merged them into a single fact table which contains 5  different keys. Each fact table has a date field. So my fact table as of now contains 5 date fields such as (order date, ship date and so on).

Now if I need to create a common calendar, How do I generate a common key which fetches the data fields accordingly. As per my perception about the common calendar, I need to have a common key which connects all the fact table. But in my scenario there is no common key based on all the tables because each table get connected to other through different key combinations.

Kindly let me know how to build a shared calendar if I have 5 different date with different keys contained in one fact table.

Kind regards,

Kiru

[afurtado]

Hi.

Maybe can help you.....just to find a way.....

http  :  //   community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Best,

Alessandro Furtado

[MarcoWedel · Accepted solution]

If you joined your tables into a single fact table with 5 keys and 5 date fields, then maybe you could generate a link table with dates and date types that connects to a master calendar:

Left Join (facttable)
LOAD distinct
    key1,
    key2,
    key3,
    key4,
    key5,
    AutonumberHash128(key1,key2,key3,key4,key5) as key
Resident facttable;

tabLink:
CrossTable (datetype, date)
LOAD key,
          date1,
          date2,
          date3,
          date4,
          date5
Resident facttable;

tabCalendar:
LOAD date,
        week(date) as week,
        month(date) as month,
        ...

hope this helps

regards

Marco

[#4]

Thanks so much guys, finally it got worked . I sincerely appreciate your time.

Kiru

[MarcoWedel]

You're welcome

regards

Marco

[#6]

Hello Marco,

I appreciate your time, I am looking for some modifications in the above script with a condition that when I click a shared date, then it need to show only the same date value from other date fields. Let say when I click, 1/1/2014 from shared-date then if it is present in other date fields say in ship-date and order-date then it need to show the same otherwise it need to get grayed out.

As of now it is showing a lot of other dates as well, but my scenario is to show only that particular date which I am clicking on the shared-date.

Kindly let me know.

Kind regards,

Kiru

[#7]

Hi,

We are trying this solution for a similar problem, and we are encountering some issues. When we call our Common Calendar using this Key and a Cross Table - the dates don't see each other.

For example, if I select 11/7/2015 out of the Date field - everything is greyed out in all the other datetype fields.

So my data doesn't connect to the common calendar, even though the common calendar is being created. See below.

Left Join(Orders)

Key:

LOAD

  ProjectItemID,

  ConcessionItemID,

  TicketID,

  AutoNumberHash256(ProjectItemID, ProjectID, ConcessionItemID, TicketID) as Key

Resident Orders;

TableLink:

CrossTable (DateType, Date)

LOAD

  Key,

  Date(OrderDate) as OrderDate,

  Date(CombinedShipDate) as CombinedShipDate,

  Date(ConcessionCreatedDate) as ConcessionCreatedDate,

  Date(TicketCreatedDate) as TicketCreatedDate

Resident Orders;

CALL CalendarFromField('Date', 'CommonCalendar', '');

Do you have any solutions? Or, better - how do you do this from start to finish, creating the full calendar with  Fact Keys that are not the same?