6 Replies Latest reply: Feb 22, 2010 9:44 PM by Neaz Faiyaz RSS

    What is the use of Qlikview synthetic join?

    saravanaag

      When adding two new tables into a script Qlikview is automatically creating synthetic tables by comparing column names of the tables (if both column names are same).

      Please explain why it is enabled by default in Qlikview?

      What is the purpose of this option?

        • What is the use of Qlikview synthetic join?
          AtifAhmad

          it is because, when the field names are same it makes the two fields in different table as a join,

          if more thn 1 field is same between two table, QV has no way to find out which one is the main link, so what it does is connect all the links, and hence a synthetic key.

          if you want to use two fields as link, you will have to join them together and make a third key and make that third as the only join between tables

          thr is a use for Synthetic key, but i have never founf out what yet

            • What is the use of Qlikview synthetic join?
              saravanaag

              Dear AtifAhmad,

              Thanks for your clarification. And I would like to tell you I am very new to Qlikview and just started learning it a week back.

              So synthetic key and join are two different things right?

              And synthetic key is formed only when more than one column names are same in two different tables?

              Is my understanding correct?

              People are telling like if synthetic key is formed and a large amount of data needs to be reloaded, the script takes hrs of time to load the data. So you have to avoid synthetic key.

              Is that true?

                • What is the use of Qlikview synthetic join?
                  AtifAhmad

                  welcome to the world of QlikView and happy qliking.

                  Joins are forced joins like in SQL, Links are automatic links

                  your understanding upto now is perfect.

                  yes if the script has synthetic key, it takes it very long to load cause it has to make all the possible combinations and load all the data.

                  just post your msg on the forum and i am sure some one will help you to learn a good understanding of QlikView.

                   

                  • What is the use of Qlikview synthetic join?

                    Can I ask you a question on the join?

                     

                    I have 2 tables with 1 common filed called NTID. Table # 2 has NTID of user and login time. Tables # 1 has NTID and manager id.

                    Tables # 2 comes from application login tracker and Table # 1 comes from current employee database.

                    As users join company and leave, Table # 1 has ONLY the current employee records. Tables # 2 as is maintained by the application has history of all the users that are using application and have used application in the past.

                    I am trying to get this:

                    Table # 1 Tables # 2

                    Manager id NTID NTID Login Date

                    X 1/1/2006

                    X 1/2/2006

                    Y 1/2/2006

                    Z 1/3/2006

                    P A A 1/1/2010

                    P B B 1/1/2010

                    Q C C 1/1/2010

                    Q D D 1/1/2010

                    Q E

                    In this example A,B,C,D and E are active employee where as P and Q are manager of A & B and C, D & E respectively. A, B, C & D are user of the application that is why there is login information for A, B, C & D. E is the odd one out. X, Y & Z are ex-employee who used application in past are not in people database coz they have left the company.

                    I want to make a bar char with 'Manager id' as dimension and count of distinct (NTID) as expression.

                    Expected values are:

                    P = 2

                    Q = 2 but I get Q = 3.

                    How do I eliminate the last row? I am loading both the tables # 1 & # 2 as is from the Oracle databases and letting QV do the join.

                     

                    Neaz