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

How to remove $Syn 1 from my table structure ?

paulyeo11 — Thu, 08 Sep 2016 13:08:22 GMT

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

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

paulyeo11 — Thu, 08 Sep 2016 13:08:52 GMT

my qvw

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

jpenuliar — Thu, 08 Sep 2016 13:19:26 GMT

hi Paul :

Synthetic Keys

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

Anonymous — Thu, 08 Sep 2016 13:28:00 GMT

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

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

Anonymous — Thu, 08 Sep 2016 13:30:26 GMT

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

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

jonathandienst — Thu, 08 Sep 2016 13:31:17 GMT

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

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

miguelbraga — Thu, 08 Sep 2016 13:31:32 GMT

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,

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

paulyeo11 — Thu, 08 Sep 2016 14:12:38 GMT

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

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

paulyeo11 — Thu, 08 Sep 2016 14:15:54 GMT

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

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

paulyeo11 — Thu, 08 Sep 2016 14:18:11 GMT

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

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

miguelbraga — Thu, 08 Sep 2016 14:24:13 GMT

Forgot this symbol ';' try put this:

Qualify *;

Unqualify SOURCE;

Regards,

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

miguelbraga — Thu, 08 Sep 2016 14:36:12 GMT

You'll need to create the Key that I've told you in this 3 tables:

Then comment 2 SOURCE field in 2 of the 3 tables, and you're good to go

Regards,

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

paulyeo11 — Thu, 08 Sep 2016 22:03:36 GMT

Hi Miguel

Now working fine after i place ; at the end of the script.

I notice that this is good , because it make my data model very neat.

thank a lot.

Paul

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

paulyeo11 — Thu, 08 Sep 2016 22:07:03 GMT

Hi Miguel

I only use my old script :-

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

I never use your script :-

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

because i get error.

Now this is my data model. it is correct. ?

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

paulyeo11 — Thu, 08 Sep 2016 22:09:47 GMT

Hi Miguell

I just notice that all my chart and table no more working . Because all field name have changes. Now i learn one more thing , need to make my data model okay before i go ahead to create table and chart.

Paul

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

paulyeo11 — Thu, 08 Sep 2016 22:12:03 GMT

Hi Miguel

I cannot comment the other table SOURCE , because the qvw file i use are simple , in my actual app each table have 5 data file , diff ny source field for each country.

Paul

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

miguelbraga — Fri, 09 Sep 2016 08:28:02 GMT

Hey there,

Sorry for the delay, but your result is correct. Using that data model you will get the best performance and a clean solution You've achieve what you were looking for

Regards,

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

effinty2112 — Fri, 09 Sep 2016 08:52:55 GMT

Hi Paul,

Try:

Keys:

Load

ARKey,

SOURCE

Resident AR_TABLE;

Load

ARKey,

SOURCE

Resident ARSummary;

Load

ARKey,

SOURCE

Resident Q_DOD_2016;

Link:

Load

Distinct

AutoNumber(ARKey & '|' & SOURCE,'LinkID') as LinkID,

SOURCE

Resident Keys;

Drop Table Keys;

Left Join(AR_TABLE) LOAD * Resident Link;

Left Join(ARSummary) LOAD * Resident Link;

Left Join(Q_DOD_2016) LOAD * Resident Link;

Drop Fields ARKey, SOURCE From AR_TABLE, ARSummary, Q_DOD_2016;

Gives this DM:

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

paulyeo11 — Fri, 09 Sep 2016 09:19:19 GMT

‌HI Andrews

MAy I know you read my QVW Using Binary load and you add those script to create a link table it is ?

THank you very much for your sharing .

I Will test it when I got my PC

PAUL

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

effinty2112 — Fri, 09 Sep 2016 09:29:28 GMT

Hi Paul,

Hope it works for you. I removed the field ARKey from the data model, if you need it to remain then add the bold line shown below. Then ARKey will then appear in the link table and be selectable.

Kind regards

Andrew

Link:

Load

Distinct

AutoNumber(ARKey & '|' & SOURCE,'LinkID') as LinkID,

ARKey,

SOURCE

Resident Keys;