topic Re: How to remove synthetic keys in QlikView

How to remove synthetic keys

derekjones — Mon, 23 May 2011 10:28:29 GMT

Hi All

I've read the disussions around synthetic keys being acceptable (or not) and in my example I'm pretty comfortable keeping the one it creates, however just for my own understanding I have no idea on how to remove it if it were necessary to remove synthetic keys. Basically I have 4 tables:

Sales Table = Contains sales data by customer and employee.

Named Account List = Contains which employee owns which customer.

I then have an Employee Table to store all employee info and a Customer Table which contains all Customer Info, that way the Sales and named Account tables just work off ID's (Is this the right way to a qlikview database design?).

Qlikview makes the synthetic joins between Customer_ID and Employee_ID as expected. However when I do try and remove the synthetic key (by creating my own NamedKey) the synthetic key still exists becuase the Employee Info and Customer Info are in seperate tables.

So just for my own better understanding of how to structure the tables and prevent synthetic keys, could anyone explain how I could do this in the attached example?

My thanks in advance for your guidance,

Re: How to remove synthetic keys

prieper — Mon, 23 May 2011 10:33:16 GMT

The purpose of the "NamedAccountList" and consequently the creation of "NamedKey" is not quite clear.

The application should also run without.

Otherwise you may need to rename fields in one of your tables in order to prevent the automatic linking.

HTH
Peter

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 10:35:15 GMT

Oh, here's the Excel file with the table data I've used, if required for testing a different type of load...

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 10:43:16 GMT

Hi Peter

Yes, the NamedKey is not required as the synthetic key does the job, however I'm just trying an exercise of understanding how I could remove the synthetic key by creating my own key. In my example, I'm struggling to do this.

The purpose of the NamedAccountList table is perfectly exampled in my data. RepA has only sold to Customer1, however he looks after both Customer1 and Customer2. I would want to report both customers under his ownership which would not be possible if I just used the Sales table.

Also, please be aware that the NamedAccountsList info could not also be stored in the Customer table as this is not one-to-one i.e. Two reps could look after one customer hence the NamedAccountsList table will store two entries for the same customer.

Hope this helps understand my data structure.

Re: How to remove synthetic keys

kaushiknsolanki — Mon, 23 May 2011 10:57:00 GMT

Hi,

Here is your answer.

Hope this will help you

Regards,

Kaushik Solanki

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 11:13:19 GMT

Hi Kaushik

Nice try, but you'll see in the attached (which is your qvw with Rep A selected and object TEST_TABLE added), by not joining the customer on the sales table, when I try and report Qty Sold for Rep A it puts Qty = 2 against both Customers, when if you look at the sales table it only sold against Customer1.

Also it's worth noting that potentially there could be sales by customers who are not named to a rep, therefore the customer info needs to be also linked to teh sales table as just linking by NamedAccountList will only give back Customer data for any named accounts.

Re: How to remove synthetic keys

kaushiknsolanki — Mon, 23 May 2011 11:19:42 GMT

Hi,

Do changes in script.

Goto Sales Table, replace the Employee_id with Customer_id.

Regards,

Kaushik Solanki

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 12:09:46 GMT

Thanks again

Getting closer, that worked for this particular example but potentially there could also be reps who have sold in sales table but are not named to any accounts e.g. Historic reps that have left the company who are now not named to any accounts but we still need to report their sales. I need to include EMPLOYEE_NAME in the sales table, but using a different name (so tables do not link) means I do not have a single Employee Name field to use for selections and reporting.

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 12:25:36 GMT

Attached is a more complete example of my data, in the attached I've added a record for a historic rep example (RepB). I've also added another existing named rep to Customer1 as potentially there could be 2 reps named to the same account. The example attached reports correctlly i.e. RepA sold 2 into Customer 1, RepB sold 5 and rep C sold 1. Selecting RepA gives a list of Customer1 and Customer2 sales (even though Customer2 did not have an entry in Sales table.

This example with a Synthetic key works perfectly, I'm just trying to ascertain how you can replicate the same result without creating a synthetic key.

Re: How to remove synthetic keys

kaushiknsolanki — Mon, 23 May 2011 12:27:18 GMT

Hi,

Yes you can give a different name, but the name should be given such that it is recognised easily.

Regards,

Kaushik Solanki

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 12:32:55 GMT

So in this example are we saying that without using a sythetic key there is no way to replicate the same structure? i.e. You have to use two fields for Employee name and know which one you are using for which report and not able to share the object.

Is this therefore an example of where a synthetic key should be used as you cannot get the same result without one?

Re: How to remove synthetic keys

kaushiknsolanki — Mon, 23 May 2011 12:36:51 GMT

Hi,

Actually where ever you require 2 fields to be joined, there will be a synthetic key. (This is what i have experienced till now).

Regards,

Kaushik Solanki

Re: How to remove synthetic keys

derekjones — Mon, 23 May 2011 13:40:51 GMT

That's what I can't understand, why is there a resistance to synthetic keys when there appears to be no workaround in certain scenarios.

If you could get the same result without a synthetic key I would half understand to create the joins yourself, but in the example I have provided I have no option but to create a synthetic key if I want to share the employee name field.

Thanks again for your help.