3 Replies Latest reply: Mar 3, 2015 7:59 AM by jeremias oro RSS

    $Syn in Data Model

    Tang man

      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.

        • Re: $Syn in Data Model
          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.

          • Re: $Syn in Data Model
            Michael Tarallo

            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

            • Re: $Syn in Data Model
              jeremias oro

              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