3 Replies Latest reply: Aug 26, 2011 4:25 PM by Jamie Weber RSS

    Multiple joins to a code table

      How do I join multiple fields in a single table to a common code table?

      Help!!!  I am stuck on a high visibility project.

       

      Here is the problem (simplified):

      Table A has the following structure

       

      User_ID     Char(3)

      Current_state_of_residence_code    Char(2)

      Previous_state_of_residence_code    Char(2)

      First_state_of_residence_code     Char(2)

       

      then there is the state code table:

       

      State_code    Char(2)

      State_name   Varchar(100)

       

      All three of the the attributes in Table A

      relate (are joined to) the same state code table values.

      Since the names of the attributes are not the same, QV does not make the join.

       

      I was instructed to do the following to the state code table:

       

      Current_state_of_residence_code   Char(2)

      Previous_state_of_residence_code  Char(2)

      First_state_of_residence_code    Char(2)

      State_name  Varchar(100)

       

      where the three values in each row are identical:

       

      Data Example:

       

      Table A

      User_ID                                        001   002

      Current_state_of_residence_code    AZ    OH

      Previous_state_of_residence_code  OH    null  

      First_state_of_residence_code        WY   OH

       

      State code table:

      Current_state_of_residence_code    AZ    OH   WY

      Previous_state_of_residence_code  AZ    OH   WY

      First_state_of_residence_code       AZ     OH   WY

      State_name                                 Arizona Ohio Wyoming

       

      The problem with this approach is that QV creates a synthetic key

      Syn #1

      Current_state_of_residence_code

      Previous_state_of_residence_code

      First_state_of_residence_code

       

      The synthetic key will NEVER join to any row in Table A unless the person's current, previous and first states of reseidence just happened to be the same.

       

      How do I join multiple fields in a single table to a common code table?

      Help!!!  I am stuck on a high visibility project.

        • Re: Multiple joins to a code table
          Jamie Weber

          It's probably not the most elegant solution, but you could create 3 identical tables with different field names (corresponding to each state_of_residence). Using Inlines, the structure would be something like this:

           

          State:
          LOAD * INLINE [
              State_code, State_name
              AZ, Arizona
              OH, Ohio
              WY, Wyoming
              null, None
          ];

           

          Data:
          LOAD * INLINE [
              User_ID, Current_state_of_residence_code, Previous_state_of_residence_code, First_state_of_residence_code
              001, AZ, OH, WY
              002, OH, null, OH
          ];

           

          StateC:
          LOAD
              State_code as Current_state_of_residence_code,
              State_name as Current_state_of_residence_name
          Resident State;

           

          StateP:
          LOAD
              State_code as Previous_state_of_residence_code,
              State_name as Previous_state_of_residence_name
          Resident State;
             
          StateF:
          LOAD
              State_code as First_state_of_residence_code,
              State_name as First_state_of_residence_name
          Resident State;

           

          DROP Table State;

           

           

          • Re: Multiple joins to a code table
            John Witherspoon

            If all you're doing is defining state names, I'd suggest turning your state code table into a map, and then applying that map to all three fields to load the names into the main table.  This sort of denormalization is not a problem for QlikView, and may in fact slightly improve performance.  Something along these lines:

             

            [State Code Map]:
            MAPPING LOAD
            State_code
            ,State_name
            FROM wherever
            ;

            [Table A]:
            LOAD
            User_ID
            ,Current_state_of_residence_code
            ,Previous_state_of_residence_code
            ,First_state_of_residence_code
            ,applymap('State Code Map',Current_state_of_residence_code,Current_state_of_residence_code) as Current_state_of_residence
            ,applymap('State Code Map',Previous_state_of_residence_code,Previous_state_of_residence_code) as Previous_state_of_residence
            ,applymap('State Code Map',First_state_of_residence_code,First_state_of_residence_code) as First_state_of_residence
            FROM wherever
            ;

             

            Alternatively, if you want to be able to look up users by state regardless of when they were in that state (first, previous or current), you could instead (or in addition) do this:

             

            [State Codes]:
            LOAD
            State_code
            ,State_name
            FROM wherever
            ;
            [Table A]:
            CROSSTABLE (Residency,State_code)
            LOAD
            User_ID
            ,Current_state_of_residence_code as Current
            ,Previous_state_of_residence_code as Previous
            ,First_state_of_residence_code as First
            FROM wherever
            ;

             

            Each user now has three rows in Table A instead of 1.  Each row corresponds to values of an additional field, Residency.  It has values 'Current', 'Previous' and 'First'.  There is only one State_code field, and only one State Codes table.  If you select a State_name, you will get all users that currently reside there, previously resided there, or first resided there.  To narrow this list down further, you would need to make a selection in the Residency field.

             

            So it all depends on what you're after.  You can combine the two approaches if you want even more flexibility, but having that many different fields with similar names will probably be confusing.