topic Re: Multiple joins to a code table in QlikView

Multiple joins to a code table

Fri, 26 Aug 2011 14:40:12 GMT

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

Fri, 26 Aug 2011 19:37:03 GMT

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

johnw — Fri, 26 Aug 2011 19:52:03 GMT

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.

Multiple joins to a code table

Fri, 26 Aug 2011 20:25:37 GMT

I like John's mapping table approach.