Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tuanmaciek
Contributor

Date from different tables

Hi,

I've created a master calendar in my set analysis. I've got few tables with different types of data (i.e. budgeted, realised etc.) but they all have date dimension.  I would like to have them all connected to the master calendar. Is this correct and the best solution to that, that I add to Master Calendar a separate row with date to connect invidually to the tables?

I mean if in tables (Orders, WorkTime, Wages) I've got column named YearMonth (YYYYMM), I need to rename them so that they would be different like YearMonthOrd - for orders, YearMonthWT, YearMonthW and then add the same row to master calendar like it's shown below?

I would like to avoid creating new dimensions and have in master calendar only one universal value YearMonth

MasterCalendar:

Load

  TempDate AS DocumentDate,

  Year(TempDate)&  Num(Month(TempDate),'00') AS YearMonthOrd,

  Year(TempDate)&  Num(Month(TempDate),'00') AS YearMonthWT,

  Year(TempDate)&  Num(Month(TempDate),'00') AS YearMonthW,

  week(TempDate) As Tydzien,

  Year(TempDate) As Rok,

  Year(TempDate)&  Num(Month(TempDate),'00')  AS YearMonth,

  Month(TempDate) As Miesiac,

  Day(TempDate) As Dzien,

Tags (1)
1 Solution

Accepted Solutions
tuanmaciek
Contributor

Re: Date from different tables

I've analysied my problem once again and invented the solution which I described

Circular reference caused by canonical date

10 Replies
arvind654
Honored Contributor II

Re: Date from different tables

Look for common fields in each table.

I had same situation, 4 date fields in single table...

I just converted one of the fields to Date(FieldName) as Dates

And  in Master Calendar, Date(TempDate) as Dates.

You don't have to link all dates to master, just one is enough (Fact Table).

If feasible can you post your script or date model screen shot or perhaps a sample qvw?

And of course there are many different ways to do this, but I achieved it in different way and worked perfectly fine for me...

Cus Master will have all Calendar dates for years and your field will have only dates related to your data.

Thanks.

tuanmaciek
Contributor

Re: Date from different tables

Hi arvind654,

I attach you my qvf file. It is filled with sample data. There are some language differences between thos versions, because dimension names are in Polish language.

Case I've mentioned reffers to tables ZleceniaUliceView, CzasPracy (, WynagrodzeniaWskaznikiView, WynikiSzczegolView. They are connected to master calendar with the fields ...RokMiesiac (YearMonth). The last table (WynikiSzczegolView - fact table) is the only that connects to master calendar by document date (DataDok). First three tables has only information about the year and the month.

I'm not sure of what you mean. I don't have many date dimensions in one table, but many tables with one date dimension.

Can you look at my qvf file?

Maciek

okolyug16
Contributor II

Re: Date from different tables

A combination of multiple master calendars and canonical dates may work in this scenario; See the following blog posts for details:

Why You sometimes should Load a Master Table several times

Canonical Date

tuanmaciek
Contributor

Re: Date from different tables

Ugonna Okoli,

I've changed my model using hints from the blog You suggested. I;ve created two separate master calendars. But I don't know how to join them into one DataBridge as it was shown in the blog. Could you help me to create the code fragment?

MCModel_161227.png

This is the fragment from the load editor:

MC2_161227.png

okolyug16
Contributor II

Re: Date from different tables

Hi,

It's a bit tricky without really knowing your data but from what I understand the Canonical date approach needs a relationship between the dates, like the example in the blog post where all dates tie back to the OrderID and I don't see that common field in GLDataview and SalariesIndicatorsView.

I'm thinking multiple master calendars(without the canonical dates) could work in your scenario.

ahaahaaha
Honored Contributor

Re: Date from different tables

I looked at your data model in the attachment qvf. In my opinion everything is implemented correctly. To work with the date in the filter can be used any of the available formats. If you want to use a format different from the existing, it will automatically create another field with required format during loading master calendar.

tuanmaciek
Contributor

Re: Date from different tables

Thanks, but that Was something I Was affraid of, that I can't connect those data in one easily.

tuanmaciek
Contributor

Re: Date from different tables

I thought it may be some clue that there is connection betweent CostCenterId in GLDataView and BudgetCostCenterId from SalariesIndicatorsView. They are linked together in StructureView.

Will it be helpful?

MC_161228.png

tuanmaciek
Contributor

Re: Date from different tables

Thanks Andrey. If I don't find any better solution I'will stay with the model I have.