Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
what is the difference between ways to avoid synthetic keys from the following keys?
1) drop field name
2) commenting field name
3) renaming field name
4) using qualify keyword
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
Employee.empno .
If you are having this issue, I suggest removing all fields that are redundant and apart of the issue.
Hope this helps.
hi,
i understood.
thank you very much.
please clarify what is UNQUALIFY statement.
where to use?
when to use?
what is the difference between QUALIFY and UNQUALIFY ?
which is the best practice in real time?
thanks in advance
Hi gmk gvk,
Please go through the below link for Qualify and Unqualify .
Unqualify is the opposite of Qualify-Stating the obvious here.
Example :
Table A has the below columns
Col1, Col2, Col3
Table B has the following columns:
Col1,Col2,Col3
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:
TableA:
load Col1,Col2,Col3
FROM (source);
Table A will be create with three columns Col1,Col2,Col3
qualify *;
TableB:
load Col1,Col2,Col3
FROM (source);
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
qualify *;
unqualify Col1;
TableB:
load Col1,Col2,Col3
FROM (source);
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
qualify Col2,Col3;
TableB:
load Col1,Col2,Col3
FROM (source);
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.
QUALIFY *;
UNQUALIFY OrderID;
Orders:
LOAD OrderID,
OrderName,
CustomerName,
FROM xxx.xls;
UNQUALIFY *;
This will result in a table with fields like this:
OrderID
Orders.OrderName
Orders.CustomerName
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
prdid,
prdname,
unitcost...etc
If we use qualify statement to product table then the result would be
prdid.product,
prdname.product,
unitcost.product
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
Qualify*;
Unqualify suplrid,comp_name
supliers:
load suplrid,
comp_name,
addr,
city
from<>;
Unqualify*;
//then the result would be in the data-model in the table we can view with field names
suplrid,comp_name,addr.supliers,city.supliers
//If we are not using unqualify then the resullt would be
suplrid.supliers,comp_name.supliers,addr.supliers,city.supliers
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
products:
load*
from <>;
categories:
load*
from <>;
I hope this helps you
Please correct me if anything wrong.
hi all,
thank you very much.
my doubt is clarified.