How to remove synthetic keys

[derekjones]

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,

[prieper]

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

[derekjones]

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

[derekjones]

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.

[kaushiknsolanki]

Hi,

    Here is your answer.

    Hope this will help you

Regards,

Kaushik Solanki

[derekjones]

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.

[kaushiknsolanki]

Hi,

    Do changes in script.

    Goto Sales Table, replace the Employee_id with Customer_id.

Regards,

Kaushik Solanki

[derekjones]

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.

[derekjones]

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.

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki