topic Re: Regarding shared calendar with different keys in QlikView

Regarding shared calendar with different keys

Sat, 22 Nov 2014 22:57:12 GMT

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

Re: Regarding shared calendar with different keys

afurtado — Sat, 22 Nov 2014 23:30:12 GMT

Hi.

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

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

Best,

Alessandro Furtado

Re: Regarding shared calendar with different keys

MarcoWedel — Sat, 22 Nov 2014 23:49:26 GMT

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

Re: Regarding shared calendar with different keys

Mon, 24 Nov 2014 19:20:28 GMT

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

Kiru

Re: Regarding shared calendar with different keys

MarcoWedel — Mon, 24 Nov 2014 19:45:26 GMT

You're welcome

regards

Marco

Re: Regarding shared calendar with different keys

Wed, 26 Nov 2014 21:20:46 GMT

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

Re: Regarding shared calendar with different keys

Tue, 17 Nov 2015 22:20:40 GMT

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?