9 Replies Latest reply: Sep 6, 2011 8:35 PM by John Witherspoon RSS

    How to link two fields from one table to another ?

    Josue Andrade

      Hello,

       

      I have a table of Location, with the Fields ID_Location ,Info_Location and so do on.

       

      Then I have another table, about routes, with the fields ID_Origin_Location, ID_Destination_Location, and Distance.

       

      The problem is, I need to get the info of the location using the IDs, of  the Origin Location and the Destination Location,

      but if I use the same names for the ID fields in the load script, Qlikview complains about cyclic reference.

       

      How can I solve that ??  Any help is much appreciated !!

       

      Thanks !!

      Josué

        • How to link two fields from one table to another ?
          Toni Kautto

          I would suggest that you consider the possibility to join the two tables into one table.If that is not a good option for you then just add a keyfiled to the tables and rename the identical field on at least one of the table to avoid the association. The principle below should be applicable.

           

          T1:

          LOAD

               F1 & F2 as KeyField,

               F1 as T1.F1,

               F2 as T1.F2,

               F3

          FROM file.qvd;

           

          T2:

          LOAD

               F1 & F2 as KeyField,

               F1 as T2.F1,

               F2 as T2.F2,

               F4

          FROM anotherfile.qvd;

          • Re: How to link two fields from one table to another ?
            John Witherspoon

            I'm not sure I understood.  Let's say you currently have this structure and data:

             

            ID_Origin_Location, ID_Destination_Location, Distance
            1, 2, 5

             

            ID_Location, Info_Location
            1, Bob's House
            2, Carla's House

             

            One thing you could do is duplicate the location table:

             

            ID_Origin_Location, ID_Destination_Location, Distance
            1, 2, 5

             

            ID_Origin_Location, Info_Origin_Location
            1, Bob's House

            2, Carla's House

             

            ID_Destination_Location, Info_Destination_Location
            1, Bob's House

            2, Carla's House

             

            A different thing you could do is convert location columns into location rows on a new table:

             

            ID_Route, Distance
            1, 5

             

            ID_Route, Location_Type, ID_Location
            1, Origin, 1
            1, Destination, 2

             

            ID_Location, Info_Location
            1, Bob's House
            2, Carla's House

             

            Both approaches are useful in different ways.  It just depends on how you want to interact with your data.

              • Re: How to link two fields from one table to another ?
                Josue Andrade

                Hello John,

                 

                I think that the first approach is not good, because of the huge number of records I have.

                I'll try the second one here. I'll let you know if it worked for me.

                 

                Thank you for all your help, people !!

                Josué

                  • Re: How to link two fields from one table to another ?
                    John Witherspoon

                    I wouldn't worry about the huge number of records in the first method, except possibly in terms of load time.  QlikView's compression should in theory mean that the cloned table takes almost no memory at all.  As far at the load goes, I'd hope that your table of locations loads from a QVD.  Do both loads from the QVD using an optimized load, and the added time should be rather small, even if you have ten million locations you're managing.  The below should give you optimized loads.  I've also reduced the tables to only the required rows, which might help a little with load time and memory, and is a good idea regardless so that your list boxes don't advertise origins or destinations that don't exist.

                     

                    Origins:
                    LOAD
                    ID_Location as ID_Origin_Location
                    ,Info_Location as Info_Origin_Location
                    FROM Location.qvd (QVD)
                    WHERE exists(ID_Origin_Location,ID_Location)
                    ;
                    Destinations:
                    LOAD
                    ID_Location as ID_Destination_Location
                    ,Info_Location as Info_Destination_Location
                    FROM Location.qvd (QVD)
                    WHERE exists(ID_Destination_Location,ID_Location)
                    ;

                     

                    As I indicated, what method you use is more a matter of how you want to interact with your data.  Expanding on that a little, with the first method, you can easily pose a query like "Show me all data associated with the route that goes from Bob's House to Carla's House".  You can't do that easily with the second method.  But in the second method, you can easily pose a query like "Show me all data associated with Bob's House, regardless of whether the route starts or ends there".  You can't do that easily with the first method.  Combining both allows you to pose both queries easily, but having three different location fields could get confusing for your users.  I've generally used one or the other approach, not both, but I HAVE done both in the same application before (where my users are fairly sophisticated data analysts already).