17 Replies Latest reply: Feb 28, 2017 1:35 AM by Jia Sheng Loh RSS

    Help in Synthetic Key

    Jia Sheng Loh

      Hi experts,

       

      I need help in synthetic key.

       

      My objective: to create a master calendar so that I can visualise a 3 year trend chart base on fiscal year(1 April-31 March).

       

      My data scrip:

       

      [HTD_AWARD_DATA$]:

      LOAD

      [AWARDED_DATE1]       as [A Awarded Date],

      .

      .

      .

      .

      From

      FROM [.......xls]

       

      //Sort by Year
      Fact:
      load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;


      drop table [HTD_AWARD_DATA$];

       

       

      My master calendar script:

       

      MinMax:
      LOAD
      Max([A Awarded Date]) as MaxDate,
      Min([A Awarded Date]) as MinDate
      RESIDENT Fact;

       

      LET varMinDate = num(Peek('MinDate',0,'MinMax'));
      LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
      LET vToday = $(vMaxDate);


      Datefield:
      LOAD date($(varMinDate)+IterNo()-1) AS Datefield
      AUTOGENERATE (1)
      WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);


      Set vFM = 4 ;                                             // First month of financial year

       

      Calendar:


      Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
           Dual(Month, fMonth)              as FMonth,         // Dual financial month
                *;
      Load Year + If(Month>=$(vFM), 1, 0)  as fYear,           // Numeric financial year,
               Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric financial month
                *;
      Load

                Datefield as [A Awarded Date],

                Year(Datefield)                  as Year,      // Your standard master calendar
               Month(Datefield)             as Month,

      Datefield
      Resident Datefield;
      DROP Table Datefield;

       

       

      Result: When I load the data, it show that

       

       

      $Syn 1 = A Awarded Date+Year

       

       

       

      How do I clear this synthetic key or the key should be there?

       

       

      Regards,

      Frederic

       

       

        • Re: Help in Synthetic Key
          Shiva Nagesh

          You are already creating Year in you table from Awarded date and again calculating year in calender with the same name and same value. I think you need not create year in fact table as it is being calculated in the Calender. If your requirement says to have both, try using different name for year in either Fact table or Calender. That will avoid synthetic keys.

            • Re: Help in Synthetic Key
              Jia Sheng Loh

              Hi Shiva,

               

              I could not understand what do you mean. Do you mean if I do not need both, I will have to delete

               

              //Sort by Year
              Fact:
              load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;


              drop table [HTD_AWARD_DATA$];

               

              then

               

              MinMax:
              LOAD

              Max([A Awarded Date]) as MaxDate,

              Min([A Awarded Date]) as MinDate

              RESIDENT [HTD_AWARD_DATA$];

               

               

              ?

               

            • Re: Help in Synthetic Key
              Jayaseelan K

              Hi,

                  I your script your using field name Year in two times obviously synthetic key will come. For more clarity you may refer following link https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/


              Else please post your .qvf file we will clear the synthetic key.


              Thanks,

               

              • Re: Help in Synthetic Key
                Rahul Pawar

                Hello Jia,

                 

                Hope that you are doing well!

                 

                By analyzing the script provided by you, I have few queries. Your inputs on this will help us to close this issue.

                 

                Why is the purpose of below given statements? What is the use of Year field though we have Year from MasterCalendar?

                 

                //Sort by Year
                Fact:
                load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;
                

                 

                If you drop/remove the Year field from above load statement then you can get rid of Synthetic key.

                 

                Trust that this will be helpful.

                 

                Regards!

                Rahul

                  • Re: Help in Synthetic Key
                    Jia Sheng Loh

                    Hi Rahul,

                     

                    actually I am not very sure about that table, it was a help from the community to create a me calendar. So if I do not need the script, I shall delete it?

                     

                    Then where shall I resident MaxDate and MinDate to? Afterall I would need to MaxDate and MinDate to continue the script right?

                     

                    LOAD
                    Max([A Awarded Date]) as MaxDate,

                    Min([A Awarded Date]) as MinDate

                    RESIDENT Fact;

                     

                    Regards,

                    Jia

                      • Re: Help in Synthetic Key
                        Rahul Pawar

                        Hello Jia,

                         

                        Generally, MasterCalendar will hold all kind of date related fields such as Year, Quarter, Month, Week, Day & DateKey etc. and Fact table will contain a DateKey and rest of measures/metrics. Using DateKey Qlik can join data from MasterCalendar and Fact table.

                         

                        I believe, you can drop the Year field from Fact Table. You can make use of Min. Date and Max. Date using Resident table to define start and end date of MasterCalendar table.

                         

                        Hope this will be helpful.

                         

                        Regards!

                        Rahul

                    • Re: Help in Synthetic Key
                      Rajesh RS

                      Hi Jia,

                       

                      The fields A Awarded Date and Year are appearing in both of your tables (Fact table and Calendar table). So that the synthetic key has been created.


                      Solution: Create a key by combining both the fields.



                      Regards,

                      Rajesh R. S.

                      • Re: Help in Synthetic Key
                        ishan Bhatt

                        Hi Jia,

                         

                        As per your script there are two columns having same names (A Award Date , Year) in two different tables.

                        Your script

                         

                        Table1:                                        Calendar:

                        A Award Date                              A Award Date

                        Year                                            Year

                         

                        Because of this it shows synthetic key. to handle this simply comment or remove Year column from the Table 1.

                         

                         

                         

                        Thanks,

                        Ishan

                        • Re: Help in Synthetic Key
                          Priyanka Sawant

                          Hi Kindly rename the year as Award_Year

                           

                          Fact:

                          load *,

                          year([A Awarded Date])as Award_Year

                          resident HTD_AWARD_DATA$;

                          drop table [HTD_AWARD_DATA$];

                           

                          and load the data.. synthetic key will be removed

                          • Re: Help in Synthetic Key
                            Jia Sheng Loh

                            Thanks for everyone's help. Everyone said the same thing is just that I am to dumb to understand. HAHAHAHA!

                             

                            But I do manage to resolve the syn key.

                             

                            THANK YOU!