Same column name in different table results in Incorrect data by producing synthetic key

[#1]

I have a scenario where , there are two table

Table A:

Id , Doc Type, Is Required , Comments

ID	Doc Type	Is Required	Comments
1	Doc 1	Yes
1	Doc 2	Yes
2	Doc1	Yes
2	Doc3	No	will be provided later

Table B:

Id , Doc Type, Doc Name , Location

ID	Doc Type	Doc Name	Location
1	Doc1	test.docx	D:/User
1	Doc2	test1.docx	D:/User
1	Doc4	test2.pdf	D:/User
2	Doc2	test3.pdf	D:/User
2	Doc3	test4.txt	D:/user

Table A is Rule table which displays what all documents are needed for a particluar Id

Table B holds actual documents attached for Corresponding Id. One can attached any document

I need to Create two tables with same structure in Qlikview , and a filter Doc Type. When user select a  Doc Type from filter or click any Doc Type in any  table ,  both table should show data for that Doc Type only (for all related Ids). Ex: when Doc2 is selected

1 row from table A and 2 rows from table B get displayed.

When I am writing the query , it is generating synthetic keys and data is coming incorrect. Some rows gets filter out.

Please suggest an approach to do this.

[jonathandienst]

Hi

I would join these two tables rather than creating separate tables in your model unless there is a huge difference in the cardinality of the tables.

If you keep them separate, you will get a synthetic key to handle the multifield association. This is not an error, and should not cause any serious problems. But if you want to get rid of the synthetic key, you can create a compound key using something like

     [ID] & '-' & [Doc Type]

You should create the key in the table loads for both tables, and choose which table should not also load the ID and Doc Type. This approach works if one table contains all the possible IDs and Doc Types.

BTW - this is simply doing manually what QV is doing automatically with the synthetic key. And that is why I would join the tables, if at all possible.

HTH

Jonathan