topic Re: Synthetic key in QlikView

Synthetic key

Mon, 11 May 2015 15:13:23 GMT

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

Re: Synthetic key

Mon, 11 May 2015 15:50:08 GMT

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.

Re: Synthetic key

Mon, 11 May 2015 20:08:20 GMT

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

Re: Synthetic key

ashwanin — Mon, 11 May 2015 20:14:43 GMT

Hi gmk gvk,

Please go through the below link for Qualify and Unqualify .

Do you Qualify?- How to use QUALIFY statement

Re: Synthetic key

Tue, 12 May 2015 00:12:52 GMT

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.

Re: Synthetic key

Tue, 12 May 2015 00:16:13 GMT

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

Re: Synthetic key

satishqlik — Tue, 12 May 2015 05:58:33 GMT

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.

Re: Synthetic key

Tue, 12 May 2015 13:40:59 GMT

hi all,

thank you very much.

my doubt is clarified.