Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

How to remove $Syn 1 from my table structure ?

Hi All

I have just create a AP AR ARSummary and Sales Table.

I like to make my table more neat .

Can some one tell me how to remove the $Syn1 ?

I no it is no affect to my app , i just want to learn how to remove it.

Paul

20 Replies
paulyeo11
Not applicable

Re: How to remove $Syn 1 from my table structure ?

my qvw

jpenuliar
Not applicable

Re: How to remove $Syn 1 from my table structure ?

hi Paul :

Synthetic Keys

balrajahlawat
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Why not try to join AR table and AR Summary table?

balrajahlawat
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Also linking of AP table through Source as Key may not be right, You can try to link them through Customer ID?

jontydkpi
Not applicable

Re: How to remove $Syn 1 from my table structure ?

The $Syn are the synthetic keys that QV produces to handle composite keys between tables, that is keys between tables that comprise more than one field.

They can be an indication of a problem in the load script or model design, which is bad, but the the synthetic keys are not intrinsically bad.

If one side of the composite key contains all the possible values of a field, you can drop the field from the other side. If neither side contains a full set of possible values, you may create a link table (but that is what the synthetic key table is doing anyway), or you may create a composite key of your own by concatenating the fields together into a key field (which is analogous to what the synthetic key is doing). You may want to do one of these to take explicit control of this process rather than letting QV handle it automatically

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
miguelbraga
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Hey there,

This is easy to solve mate

Add this code before the 4 tables that has a synthetic key:

Qualify *;

Unqualify SOURCE

If you want to keep the field named as it is, I recomend you to make a Key Field like this in script:

Autonumber(SOURCE & '-' & ARKey) as [%SOURCE-ARKey]

The above code is the best approach to make a KeyField because it takes less space in memory and improve performance. To make this work great you need to either comment the fields that are in Synthetic Table or rename it in each table.

Hope this solve your problem with ease

Regards,

MB

paulyeo11
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Hi Miguel

I get error msg when i paste below script before i load the 4 table.

Qualify *;

Unqualify SOURCE

Do i need to , to above ?

autonumber( @1:9T & '_' & 'TDSS' ) as ARKey,

I place the below script below the above script :-

Autonumber(SOURCE & '-' & ARKey) as [%SOURCE-ARKey],

I also get error msg , because SOURCE field is not available. Do you mean i need to partial reload ?

Paul

paulyeo11
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Hi Jonathan

Thank you for provide me so detail explanation,

nice to hear that :-

They can be an indication of a problem in the load script or model design

But i not sure why you said that ?

Paul

paulyeo11
Not applicable

Re: How to remove $Syn 1 from my table structure ?

Hi Balraj

Thank you for advise me to join the 2 AR table. As these 2 table are similar. and it can make data structure less complicated.

Paul