Right fields for concatenated key

[surajap123]

Hi All,

1. What is the approach to pick the correct keys to create concatenated key.

Imagine, i have two tables with multiple columns and have no understanding of data. How can i figure out the right key to make table association by renaming.

I know that higher subset ratio tells a particular field is the right key. But how can i pick that key?

2. Suppose, i found a common key between the tables, then what could be the reason to create concatenated key in this case and how to find those multiple fields as candiates for concatenated key?

Thanks!

[Digvijay_Singh]

Top Down approach helps, if you know what KPI or business analytics you need to create, you start understanding what kind of data model you will need. Second step is understanding of data, which helps to create the right model to generate objective KPIs keeping performance, maintainability etc NFR aspects in mind. Many other important factors like circular ref, synthetic keys, grain definition helps to find right fields for associations.

[marcus_sommer]

You need always an understanding what kind of data is in which field of which table to find sensible ways to concatenate/join/map or associate those data and which from them will be needed for which reason and user. There aren't any valid shortcuts to them.

- Marcus

[datanibbler · Accepted solution]

Hi Suraj,

a keyfield in a table naturally has to be unique as well as common to both the tables that you want to link. QlikView will automatically link on fields that have the same name, but that doesn't mean those are necessarily the right ones.

Find out using a straight_table_diagram which field is unique in each table -  BEFORE you link them because counting keyfields that already link tables is misleading.

When you have a field or combination of fields that are both common to the tables and unique in either one, you have a key that you can use.

=> Your objective should be to have a 1:1 or 1:n relationship, not n:n - that means, your key should be unique in at least one of the two tables.

HTH

[surajap123]

Thanks for all the inputs, it really helps.

-I agree that understanding of the data always helps to design the proper data model. But my experience is, sometimes there is no database developer and business user dont know anything about tables, they only know the business.

since i am not a sql developer, i am looking for techniques that helps me design the proper data model.

@DataNibbler - looking at table relationships definetely heps. The straight table approach you mentioned, i didnt understand.

Could you please explain, how the straigh table helps to find unique fields and how to find 1-1 or 1-n relatiionship between tables using qlikview?

Thanks!!