$Syn in Data Model

[#1]

Dear Community

I have got a $Syn problem in Data Model. It is a Star Schema with Purchasing Order as the fact table and others are the dimension tables.However, there is a $Syn problem due to the fields "Year" and "Month" in Purchasing Order, Training and Expenditure table. Actually, those table do not have linkage besides Year and Month.

I wonder QlikSense is able to generate the Time or Timeline table Automatically or manually. What I can do to solve the problem for better database design?

Thank you very much. Please give me a helping hand.

[Gysbert_Wassenaar]

You have only one synthetic key using the Year and Month fields. I don't think this is a problem. If you wish you can create a calendar table and link all the tables to calendar table. You could use a date field (e.g. makedate(Year,Month) as DateKey) to link the tables.

[Michael_Tarallo · Accepted solution]

Hi Tang Man - see attached example with Master Calendar.

Copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps (if using Desktop)

Date / Time is not generated automatically in this release, but it is something we are working towards.

If you need to get the Master Calendar to work with your data do this:

Locate the Calendar script and put in your date field here in this section:

Temp: 

    Load 

                   min(OrderDate) as minDate, 

                   max(OrderDate) as maxDate 

    Resident Orders; 

Yyour data should be loaded first on top of Calendar tab

Define the data that you load with the date field with the a table definition named Orders as in:

Your Data for example:

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[#4]

You can use a link table with Year and month in the link table.

1)put year, and month in the LT

2)Create a key like this month & '-' & year AS KEY_MONTH_YEAR

3)drop month and year from all the tables

Regards