Create Associations

[albertestrada]

I am new to Qlik, specifically QlikSense and need guidance on building Associations.  I have created two tables from the data load editor, one from a Oracle server and the other from an Excel sheet.  When I try to create the Association between tables in the Data Manager, QlikSense does not let me. Each table has a label "Generated by script" and a prompt " This table is added using the data load script, and can only be viewed in the data model viewer after data is loaded".  How can I create the Associations, since I don't see the option in the Editor or Model Viewer?

[phaneendra_kunc]

while loading data for the first time you have option to make or modify the associations. once you pass the data manager part then it will build the script and load the data.

after this i dont think there is a way u can change the associations using data manager, as far as i know. but not sure if it can be done in 3.0.

one thing you can do is to go to the load script editor and modify the Load statemets. usually Qlik associates if it finds columns with similar name.

[harvinderjohal · Accepted solution]

Field names must be exactly the same for auto-association links between tables.  Within the Data Load Editor, you can 'Unlock' the script so that you can manually model or transform the data script to address your needs.  Just NOTE, once unlocked, you won't be able to use the Data Manager as the Data Load Editor can allow for advanced or complex data modeling beyond what the Data Manager can handle.

Lets' say you have those two tables:

CustomerTable:

LOAD * Inline [

CustID, CustomerName

1, Luke

2, Leia

3, Chewbacca

4, Han Solo

];

Orders:

LOAD * Inline [

OrderID, CustomerID, Product, QTY

11234, 4, Fedora Hat, 1

12234, 2, Hair Brush, 1

13234, 3, Breath Mints, 10

14234, 1, Light Sabre, 1

];

If this was loading data from a table, I'd just alias the field name in the LOAD statement but as it's an Inline load, I'll just resident load the Orders table and make the change.

[Temp]:

LOAD *, CustomerID as CustID

RESIDENT Orders;

DROP TABLE Orders;

RENAME TABLE Temp TO Orders;

In another post, I've shared the following information on how Qlik works with data and how associations are made.  Below is the info and the link to more info:  Can we blend data in QlikSense as we do in Tableau

===================

A few concepts around Qlik.

Qlik has a concept of working with data that brings tables into Qlik's memory model.  This allows Qlik to Associate data together from tables that can be coming from various sources (ie. a Table from SalesForce can be linked with an Excel Table and a SQL table and another Cloud based source, etc, etc..).  Because it's in memory, the resulting tables that are joined are seen as one holistic data model.

Tables can be linked and if the model is exactly the same between the tables (ie. identical field names), the tables will auto concatenate.  Here's an extract from the online help:  http://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/LoadData/concatenate-tables.htm

Automatic concatenation

If the field names and the number of fields of two or more loaded tables are exactly the same, Qlik Sense will automatically concatenate the content of the different statements into one table.

Example:

LOAD a, b, c from table1.csv;

LOAD a, c, b from table2.csv;

The resulting internal table has the fields a, b and c. The number of records is the sum of the numbers of records in table 1 and table 2.

The number and names of the fields must be exactly the same. The order of the two statements is arbitrary.

Michael Tarallo hosts a weekly webinar and has recorded a full webinar video to explain more on getting started:  Qlik Sense - Getting Started Webinar 3.0 - YouTube

Associative:  http://help.qlik.com/en-US/sense/3.0/Content/Videos/Videos-assoc-selection-model.htm

To get you started, take a look at the tutorial available from the online help:  http://help.qlik.com/en-US/sense/3.0/Content/Tutorials/Tutorials-beginning-basics.htm

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.