Different sources are not properly being joined

[#1]

I have multiple data sources that i want to act like in a join. I do not actually use a join but the columns are named the same and QlikView does connect the data models successfully. According to this blog entry this should be sufficient.

As for an example, i have 3 tables in an Excel file.

• Survey: Contract / User / EMail / SurveyTime / SurveyScore / SurveyReason
• Notes: Contract / User / EMail / NoteTime / NoteText
• Userdetails: Contract / User / EMail / Phone / Address / Name

As you may have noticed, the columns "Contract", "User" and "EMail" have the same name and are supposed to be the join condition. The column "EMail" may be empty. In the QlikView data structure, all tables are connected and a $Syn1 table is created with only those three keys - this tells me the join should work.

The problem: As soon as i select a column that not all tables share, lets say SurveyScore, all Notes and Userdetails are completely vanished. The goal is to be able to select a specific SurveyScore and still have access to the userdetails.

I debugged it a little by creating a table chart including all columns. It will display the join columns and only the table specific columns are filled per row. See my demonstration below for further clarification.

• Row 1: Contract = A /  User = A / EMail = A / SurveyTime = Empty [...] / NoteTime = Empty [...] / Phone = Value
• Row 2: Contract = A /  User = A / EMail = A / SurveyTime = Empty [...] / NoteTime = Value[...] / Phone = Empty
• Row 3: Contract = A /  User = A / EMail = A / SurveyTime = Value[...] / NoteTime = Empty [...] / Phone = Empty

Thanks for the help in advance.

[varshavig12]

I am not sure,

But, you can try making a composite key of contract, user and email.

[varshavig12]

Make a composite key.

It will work.

[Anonymous · Accepted solution]

Hi Nicolas,

Try something like this:

Keys are same structure: Autonumberhash128(Contract&'-'&User&'-'&EMail) as Key

If your dimension tables have only 1 record, you can concatenate it, or joining.

Regards!

[Colin-Albert]

Create a compound key by concatenating the fields.

Adding a pipe character between each component allows you to see how the key is constructed more easily.

    Contract & '|' & User & '|' EMail as Key

Add the compound key to all 3 tables and just have the discrete fields for Contract, User & Email in one table - UserDetails would be the logical choice.

[effinty2112]

Hi Nicolas,

Check if for each combination of Contract / User / EMail in the table Survey there are records in the other tables with the same combination.

I suggest as a temporary way to check this load all your table with an additional field

Contract & '|' & User & '|' &  EMail as KeyField

Drop fields Contract / User / EMail from the tables Notes and Userdetails.

Examine the fields in the Table Viewer, look at the Subset Ratios for the KeyField in the three tables.

Cheers

Andrew