topic Cannot associate tables correctly in Qlik Sense in Management & Governance

Cannot associate tables correctly in Qlik Sense

amberalu — Sat, 16 Nov 2024 21:12:35 GMT

I utilized DATA CONNECTION to load data from SQL Server and DATA MANAGER to join/associate two tables. Some of the tables were successfully joined/associated, but others did not.

The reason for those tables which were unable to join might be as follows.

The common column (TableA.type) of the main table (TableA) has much less unique values than the common column (TableB.typePK) of the reference table (TableB). I tried to customize the association between TableA and TableB, but it failed.

For example,

Values in the common column (TableA.type) of the main table (TableA): 3,4,7

Values in the common column (TableB.typePK) of the reference table (TableB): 1, 2,3,4,5,6,7,8,9,10

The realtion between TableA and TableB on the common columns is many-to-one.

Thus, my question is how to join/associate tables based on the common columns like this situation. Can I use DATA LOAD EDITOR to associate them? How to do so? Are there other solutions for this?

I really appreciate your help!

Re: Cannot associate tables correctly in Qlik Sense

marcus_sommer — Thu, 21 Mar 2019 07:47:32 GMT

I don't know if it could be done directly within the data-manager but you could do it by going into the script. There are three main-approaches possible for such a usecase which are described here: Faster-and-simpler-than-a-link-table-in-Qlik-Sense.

- Marcus

Re: Cannot associate tables correctly in Qlik Sense

amberalu — Fri, 22 Mar 2019 19:29:49 GMT

Solved!

I used DATA LOAD EDITOR to define keys and link tables.

Here is an example using SQL Server as a data connection.

LIB CONNECT TO "DatabaseName"; # the name of the database

LOAD qlik1_col1, # column names show on Qlik

qlik1_col2 as Key,

qlik1_col3;

[Qlik Table1 ]: # table name show on Qlik

SELECT db1_col1, # column names show on database

db1_col2,

db1_col3

FROM DatabaseName.Table1Name;

LOAD qlik2_col1, # column names show on Qlik

qlik2_col2 as Key,

qlik2_col3;

[Qlik Table2 ]: # table name show on Qlik

SELECT db2_col1, # column names show on database

db2_col2,

db2_col3

FROM DatabaseName.Table2Name;

To link two tables:

1. Change the names of two common columns, which are qlik1_col2 and qlik2_col2, to the exact same name (Key).

2. Click the 'Load Data' button.

3. The tables will be loaded and linked.