Multiple joins to a code table

[#1]

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.

[#2]

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;

[johnw]

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.

[#4]

I like John's mapping table approach.