7 Replies Latest reply: May 12, 2015 9:40 AM by gmk gvk RSS

    Synthetic key

      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
          Imad Zidan

          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

              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
              Ashwani Kumar

              Hi gmk gvk,

               

              Please go through the below link for Qualify and Unqualify .

               

              Do you Qualify?- How to use QUALIFY statement

              • Re: Synthetic key
                Imad Zidan

                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
                  Sudheer Kumar Mukka

                  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
                    satish qlik

                    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

                      hi all,

                      thank you very much.

                       

                      my doubt is clarified.