1

Suppose I have two datasets. Two tables, one named marriage table showing the combination of two people in marriage, the second table showing details about people. The tables are joined via two foreign keys on PERSON_ID.

In QlikView, if I try to include these in a load using a query like the following:

sql select marriage_id, primary_person_id, seconary_person_id, marriage_start_date, marriage_end_date from marriage_table;

sql select person_id as primary_person_id, person_id as seconary_person_id, first_name, middle_name, last_name, date_of_birth from person_table;

I will get an error about how I could be leading myself to have inaccurate data, as QlikView has two potential paths to get to PERSON_TABLE. Which makes sense, but I really really hate the idea of duplicating the selects and tables like the following.

sql select marriage_id, primary_person_id, seconary_person_id, marriage_start_date, marriage_end_date from marriage_table;

sql select person_id as primary_person_id, first_name, middle_name, last_name, date_of_birth from person_table;

sql select person_id as seconary_person_id, first_name, middle_name, last_name, date_of_birth from person_table;

Is there a better way to deal with this that I'm missing?

Marisa
  • 732
  • 6
  • 22
  • I am not familiar with QlikView, but look for "sql join". The query into person table has no idea which person is which right now because it's just a query. When you're just looking at the person table, you don't know if they are even married, and have no way to know. – Kenny Ostrom May 26 '17 at 13:51
  • @KennyOstrom, that's just it--Qlikview is the one that handles the joins based on column names. If I were dealing with just SQL, it would be so much easier! – Marisa May 30 '17 at 11:19

1 Answers1

0

What you are showing in that figure is called "circular reference" (I think Qlikview calls it "Synthetic Key") and it is something that you should really try to avoid since it may make your app, not crash, but show incorrect results (which is worse). In my opinion you have two options:

Op1 - Duplicate your PERSON_TABLE so that PRIMARY_PERSON_ID will be linked to PERSON_TABLE_1 and SECONDARY_PERSON_ID will be linked to PERSON_TABLE_2.

PERSON_TABLE_1:
SQL SELECT person_id as primary_person_id,
first_name as first_name_1,
middle_name as middle_name_1,
last_name as last_name_1, 
date_of_birth as date_of_birth_1 
FROM person_table

PERSON_TABLE_2:
SQL SELECT person_id as secondary_person_id,
first_name as first_name_2,
middle_name as middle_name_2,
last_name as last_name_2, 
date_of_birth as date_of_birth_2 
FROM person_table

The problem with this option is that you have to choose different alias for each field, which is usually not very convenient depending on the type of analysis you do in your app.

Op2: Create a unique MARRIAGE_TABLE already including the data of the two people. For that you can create a SQL query with two JOINS (I will only use first and middle names for simplicity, but you can add all the other fields)

SELECT T1.*, T2.first_name as first_name_1, T2.middle_name as middle_name_1, 
T3.first_name as first_name_2, T3.middle_name as middle_name_2
FROM MARRIAGE_TABLE AS T1
LEFT JOIN PERSON_TABLE AS T2 ON Q1.primary_person_id = T2.person_id 
LEFT JOIN PERSON_TABLE AS T3 ON Q1.secondary_person_id = T3.person_id

which will result in a unique table with the following columns:

MARRIAGE_ID PRIMARY_PERSON_ID SECONDARY_PERSON_ID MARRIAGE_START_DATE MARRIAGE_END_DATE FIRST_NAME_1 MIDDLE_NAME_1 FIRST_NAME_2 MIDDLE_NAME_2
Carlos Borau
  • 1,433
  • 1
  • 24
  • 34