topic Re: Synthetickeys issue in QlikView

Synthetickeys issue

Thu, 06 Sep 2012 10:49:46 GMT

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.

Re: Synthetickeys issue

Thu, 06 Sep 2012 11:47:16 GMT

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.

Re: Synthetickeys issue

jonathandienst — Thu, 06 Sep 2012 11:50:11 GMT

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

Re: Synthetickeys issue

Thu, 06 Sep 2012 12:09:29 GMT

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.

Re: Synthetickeys issue

pgalvezt — Thu, 06 Sep 2012 13:29:58 GMT

Hope help you.

Re: Synthetickeys issue

Thu, 06 Sep 2012 14:04:58 GMT

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

Re: Synthetickeys issue

pgalvezt — Thu, 06 Sep 2012 15:02:33 GMT

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;

Re: Synthetickeys issue

pgalvezt — Thu, 06 Sep 2012 16:50:49 GMT

Could you solve the problem?

Re: Synthetickeys issue

Fri, 07 Sep 2012 08:25:59 GMT

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

Re: Synthetickeys issue

Mon, 10 Sep 2012 11:10:12 GMT

Hi all,

Anyone having any idea on this problem?

Thanks in advance

Re: Synthetickeys issue

pgalvezt — Mon, 10 Sep 2012 12:44:58 GMT

Hi, Prid Field just has one value?

Re: Synthetickeys issue

Tue, 11 Sep 2012 12:34:54 GMT

Hi,

Yes PRID has only one value currently because data got loaded from single excel file.

If we load data from multiple excel files then the PRID will have that number of values. i.e; Each excel has one PRID value.

Thanks in advance.

Regards,

Venkat

Re: Synthetickeys issue

pgalvezt — Tue, 11 Sep 2012 12:52:39 GMT

Ok, see the attached please.

Hope Help you.

Re: Synthetickeys issue

pgalvezt — Tue, 11 Sep 2012 14:30:05 GMT

If you want you can change the name Prid for all tables. But you are going to have a synthetics keys again. But check the information with that, maybe works even with SK.

Hope Help You

Re: Synthetickeys issue

pgalvezt — Wed, 12 Sep 2012 13:34:13 GMT

Hi,

Could you solve the problem?

Re: Synthetickeys issue

Wed, 12 Sep 2012 14:15:54 GMT

Hi Pgalvezt,

Really thanks for your co-ordination on this post from last few days.

But i'm really confused a lot to maintain perfect relation without synthetic keys in current data.

I will attach the final and original data application to you in another few minutes.

And i'm sorry to disturb you on this more times. But i hope you can understand my problem and supporting on this.

Thanks in advance.

Re: Synthetickeys issue

Thu, 13 Sep 2012 12:19:16 GMT

Hi,

The attached app resolves my issue.

Just i had done little changes in my app by comparing previously attached app by you.

I think this application is looking good so for now and i will get back you if any help requires on this.

Really thanks for your wonderful support on this to me.

Thanks & Regards,

Venkat.

Re: Synthetickeys issue

pgalvezt — Thu, 13 Sep 2012 12:57:41 GMT

Please put the Correct Answer so the other people can understand the problem and the solution.