As you know synthetic key is related to the way you associate your tables. Which means the naming of your fields should be carefully througt of. If you have tables that are associated using more than one field , it will cause the synthetic key phenomena.
1) drop field name: It can either mean to explicitly remove the field from you script or use the drop command to drop it if it is not needed
2) commenting field name this means use the double forward slash to comment the field which really means the same as explicitly removing the field from your script.
3) renaming field name rename the field using an alias. this is done by using the AS in your script. example
Month as specialMonth
4) using qualify keyword: When using qualify QV will prefix the field with the table name. SO when you look at the
table through table view, column names will be something like table name.column_name. Example
If you are having this issue, I suggest removing all fields that are redundant and apart of the issue.
Hope this helps.
Unqualify is the opposite of Qualify-Stating the obvious here.
Table A has the below columns
Col1, Col2, Col3
Table B has the following columns:
if you create the two tables you will have three joins based on the name and will cause the synthetic key scenario,
one of the ways to resolve this is to use Qualify one of the Tables to dis associate the two table.So you will do some like:
Table A will be create with three columns Col1,Col2,Col3
Table B will be created with three columns TableB.Col1,TableB.Col2,TableB.Col3
Now lets assume that you want to create an associate between the two table and it is based on Col1.
tp do this, you need to unqualify Column Col1 in TableB
by doing this you will end with TableA and TableB associated by Col1
Unqualify basically removes the table prefix from the column name.
Tables with few column are easier to qualify or unqualify column.
in our example above you could do this for instance, instead of qualifying the whole table first and then unqualify, you could also do the following
but the above will be impractical if you have a table with many columns and you want to associate to another tables with few columns only.
I hope this helps.
Here's a good way to use QUALIFY and UNQUALIFY to bring in new tables and you want to link on one known field, but you aren't sure whether you will have name clashes with the rest.
This will result in a table with fields like this:
Very specified to use to remove the syn key(if want to rename the field names at a time)
I hope you knew about synkey
Qualify- can be used to change the all the field names at a time
suppose we product table and field names
If we use qualify statement to product table then the result would be
Unqualify-it can be used to prevent the qualify statement for the specified fields in the same table and if we are not using unqualify statement at the end of the table, then its automatically applicable to use qualify statement for below table tables with respected table names
Below exp would helps you
Initially we have 3tables(suppliers,products,categories) but if you need to use qualify statement for 1st table(supliers) except some specified fields(suplrid,comp_name) in the same table as like below
//then the result would be in the data-model in the table we can view with field names
//If we are not using unqualify then the resullt would be
and If we are not using at the end of the table
the result would be for the end of the tables abc.products,....etc and xyz.categories
I hope this helps you
Please correct me if anything wrong.