13 Replies Latest reply: May 26, 2011 8:24 AM by Byron Van Wyk RSS

    Synthetic Keys

    Byron Van Wyk

      Hi Guys,

       

      I have a few synthetic keys, from what i read on this community site, it has been advised to get rid of these due to circular referencing and looping that can occur. I am not sure how I can go about doing this, without impact the level of flexibility I wanted my EU to have. Below is a screendump of my relationship table, any help would be greatly appreciated

       

      Syn Tables.png

        • Synthetic Keys

          Hi,

           

          I also faced this problem earlier. It has been resolved here - http://community.qlik.com/message/18742#18742

           

          Please have a look at it.

           

          Thanks.

           

          Regards,

          Sachin A.

            • Synthetic Keys
              Byron Van Wyk

              Hi Sachin,

               

              Tried to open both links but none opened. Community page not found!! I have been having this problem actually with a lot of posts. Unable to open any links on threads

               

              ---------------------------------------

               

              Hi Tony,

               

              Thanks for the clarification. I can rename the fields and that would remove the syn keys, but that would also defeat the objective of my report. Someone once said that syn keys are the result of poor data modelling and in my case, that is true. I don't have much experience with things like this.

               

              -----------------------------------------

               

              I thought it best if someone can help me remove syn2 first. This has been created because I have both agentid and date in the histogramdata and agentskillgrouphalfhour data. The reason i created the histogramdata table is because the 'class function' works by counting the number of entries in that interval, I needed my agentdata to count by date and not halfhour, So the histogram data is a view from SQL that groups this information by date. Is there any other way I can use the class function and count by date rather then by each row which is by interval from my agent skill group data?

                • Re: Synthetic Keys
                  Toni Kautto

                  Please attach your QVW with data loaded to make it easier to provide improvement suggestions. If the docuemtn contains sensitive data, please use the scrambling feature to make datat unreadable;

                   

                  Settings > Document Properties > Scrambling

                    • Re: Synthetic Keys
                      Toni Kautto

                      Jsut out of curiosity, what type of datat do you have in your histogram table? To me it almost sounds like a pre calculated chart, which could be possible to remove, if this is something QlikView can calculate based on the other tables content.

                        • Re: Synthetic Keys
                          Byron Van Wyk

                          Hi Tony,

                           

                          Ok i have modified the report slightly for security reasons but have left the data in tact. Yes the histogram data is a precalculated chart as I was not sure how I can build the same thing with the granularity of my agentskillgrouphalfhour data. Hoping you know how

                           

                          ------------------------------------------------------------------------------------

                          Sravan I will try what you suggested now and report on outcome

                            • Re: Synthetic Keys
                              Toni Kautto

                              Bit limited on time so not able to provide a hands-on improvement right away, but here are some thoughts any way.

                               

                              One thing is still a bit unclear to me, what is the difference between the data in SkillGroupHalfHour and AgentSkillGroupHalfHour?

                               

                              - Remove the histogram chart, as this is quite easily presented as a chart based on the datat in the other tables.

                              - Add a calendar table so that you only use date in the fact table, and then get all other date related stuff from the calendar (See Master Calendar among my QlikViews; http://community.qlik.com/view-profile-qlikviews.jspa?view=qlikview&username=tko)

                              - Merge the data in SkillGroupHalfHour and AgentSkillGroupHalfHour into one table, since they seems to contain pretty much the same fields.

                              - Add a new field to distinguish Agents from the other in the new merged table. Or just keep the new field to contain Agent ID for agents and leave empty (Null) for others, this way you can filter out either grouping.

                                • Re: Synthetic Keys
                                  Byron Van Wyk

                                  To answer your questioms

                                   

                                  1) The difference between agent and skillgroup is the dimension or granularity. The fields are pretty much the same, except one is with agent information and the other is for the entire operation for the day by half hour. My limited knowledge on QV and its functions oftens means I find very nasty ways around my problems

                                   

                                  2) Remove the histogram chart or remove the histogramdata load???

                                   

                                  3) Will add the calendar, havent really tried using it before but will see what improvement it makes

                                   

                                  4) Am busy merging data as we speak, hopefully I do this right

                                   

                                  Thanks so much to all so far for the time you have dedicated to my problem. Much appreciated

                                   

                                   

                                  Regards,

                                  Byron

                                    • Re: Synthetic Keys
                                      Byron Van Wyk

                                      Ok, i think I've done everything you asked for. The tables look a lot better but I still need to check if everything is going to work properly. Havent fixed the charts or tables or anything after changes were made. let me know if this is looking right

                                        • Re: Synthetic Keys
                                          Toni Kautto

                                          There goes the synthetic keys!

                                           

                                          I still would leave the histogram data out of the datat model, and solve it by calculating the result in a chart object.

                                           

                                          Regarding the table merge I was thinking more in line of keeping the original names for the fields, so that you used the table more efficiently. There is no need to differentiate the content on a field name basis as you can separate it based on the AgentID value, since all rows with Null will be rows from the non-agent group.

                                           

                                          Based on what you want to present in a chart object, you might need to add simple set expressions or use Aggr() in order to get your datat grouped properly. Try out making your charts and then if you run in to trouble attach the doc and we'll see how to guide you further.

                              • Re: Synthetic Keys
                                Sravan Puppala

                                Hi Byron,


                                Synthetic keys are helpful to make a good Datamodel..

                                 

                                you want to remove the synthetic Key 2 and still maintain a good relationship then do this

                                 

                                HistogramData:

                                Load Autonumber(AgendID & Date) as AgendIDDate_Key,

                                 

                                 

                                AgendID as HistogramData_AgendID,

                                Date as HistogramData_Date,

                                 

                                 

                                ...

                                ...

                                ;

                                 

                                AgentList:

                                Load Autonumber(AgendID & Date) as AgendIDDate_Key,

                                //The following fields you can keep like that or rename

                                AgendID

                                Date

                                ;

                                 

                                Do the same procedure to remove first synthetic key

                                 

                                Regards

                                sravan

                              • Re: Synthetic Keys

                                Hi,

                                 

                                That link is working fine for me.

                                 

                                Anyway, I m attaching the PDF of the whole discussion with the files mentioned in it.

                                 

                                Please have a look.

                                 

                                Thanks.

                                 

                                Regards,

                                Sachin A.

                              • Re: Synthetic Keys
                                Toni Kautto

                                QlikView is associative, meaning that all fields with the same names will be assumed to be the same content. These  fields will be linked to each other as key fields. Synthetic keys are generated when you have more than one field in common btween two tables. A quick way to remove syntheic keys is to simply rename one of the fields. Like in your example above you might solve the problem by renaming the Date and DateTima fields to something else.

                                 

                                Circular references do not appear due to synthetic keys. As the name reveals a circular reference is when tables are linked to each other in a way that there is no clear start and end of the table relations, this will be marked by a dottedline in the table viewer.¨

                                 

                                Not knowing your project in detail it is a bit hard to give detailed advise, but based on what I see in the screenshot I am guessing you load data straight up from a datat source. Commonly the loaded date need to be reorganised to get a nice data model. For example identifying what you fact table is and then add attribute table to that to meet your requirements. The key to a good QlikView model is to achieve a star or snowflake schema in your data model.