8 Replies Latest reply: Feb 13, 2013 10:34 AM by Jose Rodriguez RSS

    connecting tables with same IDs

      hi

       

      I have 3 tables. all with the same primary key. how to i connect the first one with the second and connect the second with the third BUT NOT THE THIRD WITH THE FIRST. Qlikview connect all the 3 by default....

       

      Example:

       

      table1: primary key>>F1

       

      table2: primary key>>F1

       

      table3: primary key>>F1

       

      dfvdf.bmp

       

      I dont want all the tables to connect like this. just want "spock" to connect to "flower" and "flower" to "lyon". is it possible?

        • connecting tables with same IDs
          Roberto Postma

          If Qlikview makes a relation that you don't like to have in your model, this can often be resolved by aliassing column.

           

          Like this sample code

          LOAD id as myNotRelatedID INLINE [ ]

           

          Does that answer your question?

           

          If not, could you make a small example app with the 3 tables in it (can be fake data of course...)

            • connecting tables with same IDs

              hi Roberto, i am not trying to change the field's names. but is it possible using QUALIFY and UNQUALIFY?

                • connecting tables with same IDs
                  Roberto Postma

                  I wasn't saying that you are trying to change column names, but I think that is what you need to make sure Qlikview makes the right relations (see also swuehls answer). Qlikview just checks the column names of the loaded tables and draws a relation when the column name is similar.

                   

                  So if you don't want spock to be connected to lyon, you should (in your load script) make sure that the column name F1 does not exist anymore for that table. That can be done by

                   

                  load fieldname as other_fieldname

                   

                  Synthetic keys (the other issue you're referring to) is when you have 2 tables in your model where Qlikview can draw 2 relations... This should be avoided. So in your case there is a relation between F1 and F2 in both tables.

                   

                  A way to fix is: make a new column in both tables, like

                  - load F1 + F2 as F1F2Key

                  - removing the fields F2 and F1 from these tables (or rename them with an alias)

                  - resulting in 1 relation between the tables over the F1F2 key field...

                   

                  Does this help?

                  If not, please upload a sample app, then I'll see what I can change to make it even more clear.

                  • connecting tables with same IDs
                    Stefan Wühl

                    Technically spoken, it is normal that a syn key table is created using QV with tables linked by more than one common field.

                     

                    The question is if the syn key link is appropriate for your setting, to answer this you need to tell us more about your data and what your requirements are.

                     

                    And yes, QUALIFY and UNQUALIFY might be a solution. But this is similar to renaming fields in my opinion.

                • connecting tables with same IDs
                  Stefan Wühl

                  Yes, QV will link all three tables by default if the field names are the same.

                  But you will only get a 'working' link if you also have matching values in each of the tables.

                   

                  So if your first table and your third table have only distinct values, they won't really link. If your second table shows values that are also part of the first resp. third table field, the second table will be linked to the first and the third.

                   

                  Not sure if I make myself clear and even more unsure if I understand what your issue is. Could you post some sample lines and your expected outcome, or describe what your issue is in detail?`

                   

                  You could also duplicate the key field in the second table and rename it. Then rename the key field in the third table to the same name, so you only get links 1<-->2 and 2<-->3

                  • Re: connecting tables with same IDs
                    Jose Rodriguez

                    Maybe it's too late, but here it's my solution:

                     

                    Table1:

                    F1

                    F1 as F2

                     

                    Table2:

                    F1

                     

                    Table3:

                    F1 as F2

                     

                    With this solution you have 2 keys with the same info but you haven't Sync table and relationships are as you want.

                     

                    Regards,

                    Jose