Synthetickeys issue

[#1]

Hi all,

I am loading the entire tables from my SQL server into Qlikview. When i reloaded all the tables in qlikview then it is generating an synthetic keys.

Please find the attached screenshot of table viewer.

Whether this table viewer structure with synthetic key creates any problem at any case in the application ? And if it creates problem then how we can resolve this issue ?

Thanks in advance.

[#2]

Hello Dear,

Check that which id is primary in your table such as in 'AsisDATA' table there are 3 fields

TechID,PRID and AssetID.

Check primary key of this table and rename it like PRID as [ASIS PRID]

and same  name put in other table 'KepsDATA' like PRID as [ASIS PRID]

if this fails then you make link key like          PRID&AssetID&TechID as Key

and renam all the fields in each table.

[jonathandienst]

Hi

You could create a composite key by concatenating PROD, AssetID and TechID together and use this key in all the tables. Drop the above fields from all tables except one (which should have a complete set of the PROD, AssetID and TechID combinations). However, this is essentially just replicating the QV generated synthetic key with your own (and there is nothing intrinsically wrong with synthetic keys).

If there is no one table that contains all combinations, then create a link table with the key and the 3 fields, then add the key value and drop the link fields from all tables except the link table.

In all these cases, check the data and performance because your mileage my vary.

Another option is to concatenate the 5 tables directly linked to the syn key (I see quite a few fields which look common to several tables (*_Type, *_Shot etc), with a field to indicate the source table. I am not sure about the relationship between the data in these 5 tables, but I suspect that this would be the best approach.

Hope that helps a little

Jonathan

[#4]

Hi Jonathan, thanks for reply.

I understand to some extend what you mentioned in the post.

Please can you help in this sample app what really should happen when synthetic key occurs.

And find the attached sample app and qvd's.

Thanks in advance.

[pgalvezt]

Hope help you.

[#6]

Hi Pgalvezt, thank you very much providing developed app.

That's really good in avoiding sythentic keys, but please find the attached app that is not having proper relation with Technician column.

And technician column will not have '0' value in it and some assetid will have atleast one technician name. We are missing this functionality in your attached app.

And also missing relation link with Headder_DAQ table with other tables in table viewer.

Please can you check through this.

Thanks in advance.

Regards,

Venkat

[pgalvezt]

Hi,

In  Asset Table: rename the field Technician as Something

In Join Sheet put this:

UnionTable:

load

AseetDataAux           as AssetID,

TechAux                   as TechID,

KeyAux                    as @Id

resident UnionAux;

drop table UnionAux;

[pgalvezt]

Could you solve the problem?

[#9]

hi pgalvezt, sorry for late reply. And special thanks for your help on this.

yes it resolves one of the problem i.e; display Technician data, but still facing problem is there is no relational link to Headder table with other tables.

i tried to resolve it but no use. Please can you help on this also.

Thanks in advance.

Regards,

Venkat

[#10]

Hi all,

Anyone having any idea on this problem?

Thanks in advance