Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Synthetic Keys

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Synthetic Keys

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.

13 Replies
Not applicable

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.

Employee
Employee

Re: Synthetic Keys

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.

Not applicable

Synthetic Keys

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?

Employee
Employee

Re: Synthetic Keys

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

Not applicable

Re: Synthetic Keys

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

Employee
Employee

Re: Synthetic Keys

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.

Not applicable

Re: Synthetic Keys

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

Employee
Employee

Re: Synthetic Keys

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.

Not applicable

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.

Community Browser