Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amberalu
Contributor
Contributor

Cannot associate tables correctly in Qlik Sense

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!

1 Solution

Accepted Solutions
amberalu
Contributor
Contributor
Author

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.

View solution in original post

2 Replies
marcus_sommer

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

amberalu
Contributor
Contributor
Author

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.