Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.