Loading and Modeling Data Qlik

(1)

Qlik®_Sense 2.0.1

(2)

Qlik®, QlikTech®, Qlik®Sense, QlikView®, Sense™ and the Qlik logo are trademarks which have been registered in multiple countries or otherwise used as trademarks by QlikTech International AB. Other trademarks referenced herein are the trademarks of their respective owners.

(3)

1 About this document 10

2 Adding and managing data with the Data manager 11

2.1 Adding a new data table 11

2.2 Editing a data table 11

2.3 Deleting a data table 12

2.4 Reloading all data 12

2.5 Concatenated tables 12

2.6 Synthetic key tables 13

2.7 Adding data from files and databases 13

Adding data from an existing data source 13

Adding data from a new data source 14

Which data sources are available to me? 14

Selecting data fields from your data source 15

Selecting data from a database 15

Selecting data from a Microsoft Excel spreadsheet 16

Selecting data from a table file 16

Returning to the previous step (Add data) 17

Choosing settings for file types 17

Delimited table files 17

Fixed record data files 17

HTML files 18

XML files 18

QVD files 18

KML files 18

2.8 Adding data from Qlik DataMarket 19

Selecting data from Qlik DataMarket 19

Gaining access to Qlik DataMarket 19

Selecting data from Qlik DataMarket 20

Integrating Qlik DataMarket with data from other sources 21

Change field names 22

Edit the data load script 22

2.9 Managing data table associations 22

Viewing recommendations 23

Two data sources contain a field with related data but with different names 23 Two data sources contain fields with the same name but unrelated data 24

Two tables contain more than one common field 24

Loading data 24

Bypassing recommendations 24

3 Loading data with the data load script 26

3.1 Using the data load editor 26

Quick start 27

(4)

Creating a new data connection 28

Deleting a data connection 29

Editing a data connection 29

Inserting a connect string 29

Selecting data from a data connection 30

Referring to a data connection in the script 30

Where is the data connection stored? 30

ODBC data connections 31

Creating a new ODBC data connection 31

Editing an ODBC data connection 31

Best practices when using ODBC data connections 32

OLE DB data connections 32

Creating a new OLE DB data connection 32

Editing an OLE DB data connection 33

Security aspects when connecting to file based OLE DB data connections 33

Folder data connections 34

Creating a new folder data connection 34

Editing a folder data connection 34

Web file data connections 35

Creating a new web file data connection 35

Editing a web file data connection 35

Loading data from files 35

Selecting data from a data connection 36

Loading data from a file by writing script code 36

How to prepare Excel files for loading with Qlik Sense 36

Loading data from databases 37

Loading data from an ODBC database 37

ODBC 38

OLE DB 39

Logic in databases 39

Select data to load 39

Selecting data from a database 39

Selecting database 39

Selecting tables and views 39

Selecting fields 40

Renaming fields 40

Previewing script 41

Including LOAD statement 41

Inserting in the script 41

Selecting data from a delimited table file 41

Setting file options 41

Selecting fields 42

Renaming fields 42

Previewing the script 43

Inserting the script 43

(5)

Selecting tables 43

Selecting fields 44

Renaming fields 44

Selecting data from a fixed record table file 44

Setting file options 45

Setting field break positions 45

Selecting fields 45

Renaming fields 46

Selecting data from a QVD or QVX file 46

Selecting fields 46

Renaming fields 47

Selecting data from an HTML file 47

Selecting tables 47

Selecting fields 48

Renaming fields 48

Selecting data from an XML file 49

Selecting tables and fields 49

Renaming fields 49

Selecting data from a KML file 50

Selecting tables and fields 50

Renaming fields 50

Edit the data load script 51

Organizing the script code 52

Creating a new script section 53

Deleting a script section 53

Renaming a script section 53

Rearranging script sections 53

Commenting in the script 54

Commenting 54

Uncommenting 54

Searching in the load script 54

Searching 54

Replacing 55

(6)

Saving the load script 56

Debug the data load script 56

Debug toolbar 56

Output 57

Variables 57

Setting a variable as favorite 57

Filtering variables 57

Breakpoints 58

Adding a breakpoint 58

Deleting breakpoints 58

Enabling and disabling breakpoints 58

Run the script to load data 59

Data load editor toolbars 59

Main toolbar 59

Editor toolbar 59

3.2 Understanding data structures 60

Data loading statements 60

Rules 60

Execution of the script 60

Fields 61

Derived fields 61

Declare the calendar field definitions 61

Map data fields to the calendar with Derive 61

Use the derived date fields in a visualization 62

Field tags 62

System field tags 62

System fields 63

Available system fields 63

Logical tables 63

Table names 64

Table labels 65

Associations between logical tables 65

Qlik Sense association compared to SQL natural outer join 66

Frequency information in associating fields 66

Limitations for associating fields 66

Synthetic keys 67

Handling synthetic keys 67

Data types in Qlik Sense 68

Data representation inside Qlik Sense 68

Number interpretation 68

Data with type information 68

Data without type information 69

Date and time interpretation 70

3.3 Guidelines for data and fields 71

(7)

Upper limits for data tables and fields 72

Recommended limit for load script sections 72

Conventions for number and time formats 72

Number formats 72

Special number formats 72

Dates 73

Times 74

Time stamps 75

3.4 Understanding circular references 75

Solving circular references 76

3.5 Renaming fields 77

3.6 Concatenating tables 78

Automatic concatenation 78

Forced concatenation 78

Preventing concatenation 79

3.7 Loading data from a previously loaded table 79

Resident or preceding LOAD? 80

Preceding LOAD 80

3.8 Dollar-sign expansions 81

Dollar-sign expansion using a variable 81

Dollar-sign expansion using parameters 82

Dollar-sign expansion using an expression 83

File inclusion 83

3.9 Using quotation marks in the script 83

Inside LOAD statements 84

In SELECT statements 84

Microsoft Access quotation marks example 84

Outside LOAD statements 84

Out-of-context field references and table references 84

Difference between names and literals 84

Difference between numbers and string literals 85

Using single quote characters in a string 85

3.10 Wild cards in the data 85

The star symbol 85

OtherSymbol 86

3.11 NULL value handling 87

Associating/selecting NULL values from ODBC 87

Creating NULL values from text files 88

Propagation of NULL values in expressions 88

Functions 88

Arithmetic and string operators 89

Relational operators 89

4.1 Working with QVD files 90

Purpose of QVD files 90

(8)

Decreasing load on database servers 90

Consolidating data from multiple apps 90

Incremental load 91

Creating QVD files 91

Reading data from QVD files 91

4.2 Managing security with section access 91

Sections in the script 92

Section access system fields 92

Dynamic data reduction 93

Inherited access restrictions 94

4.3 Accessing large data sets with Direct Discovery 95

Differences between Direct Discovery and in-memory data 95

In-memory model 95

Direct Discovery 96

Performance differences between in-memory fields and Direct Discovery fields 98

Differences between data in-memory and database data 99

Caching and Direct Discovery 100

Direct Discovery field types 100

DIMENSION fields 101

MEASURE fields 101

DETAIL fields 101

Data sources supported in Direct Discovery 101

SAP 102

Google Big Query 102

MySQL and Microsoft Access 102

Limitations when using Direct Discovery 102

Supported data types 102

Security 103

Qlik Sense functionality that is not supported 103

Multi-table support in Direct Discovery 104

Linking Direct Discovery tables with a Where clause 104

Linking Direct Discovery tables with Join On clauses 104

Using subqueries with Direct Discovery 105

Scenario 1: Low cardinality 106

Scenario 2: Using subqueries 107

Logging Direct Discovery access 108

5 Viewing the data model 109

5.1 Views 109

5.2 Moving and resizing tables in the data model viewer 109

Moving tables 109

Resizing tables 110

5.3 Preview of tables and fields in the data model viewer 110

Showing a preview of a table 110

Showing a preview of a field 111

(9)

5.5 Creating a master measure from the data model viewer 112

5.6 Data model viewer toolbar 112

6 Best practices for data modeling 114

6.1 Turning data columns into rows 114

6.2 Turning data rows into fields 114

6.3 Loading data that is organized in hierarchical levels, for example an organization

scheme. 115

6.4 Loading only new or updated records from a large database 116

6.5 Combining data from two tables with a common field 116

6.6 Matching a discrete value to an interval 116

6.7 Handling inconsistent field values 117

6.8 Loading geospatial data to visualize data with a map 117

6.9 Extract, transform and load 117

6.10 Using QVD files for incremental load 119

Append only 119

Insert only (no update or delete) 120

Insert and update (no delete) 120

Insert, update and delete 120

6.11 Combining tables with Join and Keep 121

Joins within an SQL SELECT statement 122

Join 122

Keep 123

Inner 123

Left 124

Right 125

6.12 Using mapping as an alternative to joining 126

6.13 Working with cross tables 127

6.14 Generic databases 129

6.15 Matching intervals to discrete data 131

Using the extended intervalmatch syntax to resolve slowly changing dimension problems 132

Sample script: 132

6.16 Creating a date interval from a single date 134

6.17 Hierarchies 136

6.18 Loading map data 137

Creating a map from data in a KML file 137

Creating a map from point data in an Excel file 139

Point data formats 139

Number of points 141

6.19 Data cleansing 141

Mapping tables 141

Rules: 141

(10)

1 About this document

When you have created a Qlik Sense app, the first step is to add some data that you can explore and analyze. This document describes how to add and manage data, how to build a data load script for more advanced data models, how to view the resulting data model in the data model viewer, and presents best practices for data modeling in Qlik Sense.

There are two ways to add data to the app.

l Data manager

You can add data from your own data sources, or from other sources such as Qlik DataMarket, without learning a script language. Data selections can be edited, and you can get assistance with creating data associations in your data model.

l Data load editor

You can build a data model with ETL (Extract, Transform & Load) processes using the Qlik Sense data load script language. The script language is powerful and enables you to perform complex transformations and creating a scalable data model.

Make sure to see theConcepts in Qlik Sense guide to learn more about the fundamental concepts related to

the different topics presented.

For detailed reference regarding script functions and chart functions, see the Script Syntax and Chart Functions Guide.

This document is derived from the online help for Qlik Sense. It is intended for those who want to read parts of the help offline or print pages easily, and does not include any additional information compared with the online help.

Please use the online help or the other documents to learn more. The following documents are available:

l Concepts in Qlik Sense l Working with Apps l Creating Visualizations l Discovering and Analyzing l Data Storytelling

l Publishing Apps, Sheets and Stories l Script Syntax and Chart Functions Guide l Qlik Sense Desktop

(11)

2 Adding and managing data with the Data

manager

TheData manager is where you add and manage data from your own data sources, or from Qlik

DataMarket, so that you can use it in your app.Data tables contain all the tables you have loaded using Add data. Each table is displayed with the table name, the number of data fields, and the name of the data source.

You can preview the data if you click on a table. You can also edit the data table selection or delete the data table.

When you add data tables in theData manager, data load script code is generated. You can view the script code in theAuto-generated section of the data load editor. You can also choose to unlock and edit the generated script code, but if you do, the data tables will no longer be managed in theData manager.

Data tables defined in the load script are not managed in the Data manager. That is, you can see the tables and preview the data, but you cannot delete or edit the tables in Data manager.

2.1 Adding a new data table

You can quickly add a data table into your app, by clickingAdd data in Data manager or in the

¨

menu. You can add data from the following data-source types:

l Connections

Select from data connections that have been defined by you or an administrator, and folders that you previously have selected data from.

l Connect my data

Select from a new data source, such as ODBC or OLE DB databases, data files, web files or custom connectors.

l Qlik DataMarket

Select from normalized data from public and commercial databases.

2.2 Editing a data table

You can edit all the data tables that you have added withAdd data. You can add, remove, or rename fields from the data table.

Do the following:

1. Click

@

on the data table you want to edit. 2. Edit the selection of data fields.

(12)

You can only edit data tables added with Add data. Data tables that were loaded using the load script can only be changed by editing the script in the data load editor.

See: Using the data load editor (page 26)

2.3 Deleting a data table

You can only delete data tables added withAdd data. Data tables that were loaded using the load script can only be removed by editing the script in the data load editor.

Do the following:

l Click

Ö

on the data table you want to delete.

If you have used fields from the data table in a visualization, removing the data table will result in an error being shown in the app.

2.4 Reloading all data

Do the following:

l Click

°

to reload all data in the app.

2.5 Concatenated tables

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.

(13)

2.6 Synthetic key tables

When two or more data tables have two or more fields in common, this implies a composite key relationship. Qlik Sense handles this by creating synthetic keys automatically. These keys are anonymous fields that represent all occurring combinations of the composite key.

If synthetic key tables have been created as a result of adding data, you can recognize them inData tables as the name starts with $. A synthetic key table contains the synthetic key and the data fields that form the relationship.

You can't edit a synthetic key tables as they are created automatically when Qlik Sense loads data.

2.7 Adding data from files and databases

You can add data to your app quickly, by clickingAdd data in the Data manager or in the

¨

menu. You can add data from the following types of data sources:

l Connections

Add data from data connections that have been defined by you or an administrator, and folders that you previously have selected data from.

l Connect my data

Add data from a new data source, such as ODBC or OLE DB databases, data files, web files or custom connectors.

l Qlik DataMarket

Select from normalized data from public and commercial databases.

You can also drop a data file on the Qlik Sense Desktop window to start a quick data load.

Adding data from an existing data source

You can select data from connections that have already been defined by you or an administrator. These can be a database, a folder containing data files, or a custom connector to an external data source, such as Salesforce. Folders you have selected data from previously are also available here.

Do the following: 1. Click Add data. 2. Click Connections.

(14)

4. Select which specific data source you want to add data from. This differs depending on the type of data source. The most common

l File-based data sources: Select a file. l Databases: Set which database to use. l Web files: Enter the URL of the web file. l Other data sources: Specified by the connector.

5. Select the tables and fields to load.

If you want to bypass the data profiling step, you can continue to step 8. 6. Click Profile.

The data will be profiled to provide field association recommendations.

7. If the data profiling resulted in recommendations, they are displayed, and you get assistance with validating and improving table relationships, for example, adding relationships, or removing relationships by renaming one or more fields.

8. Click Load and finish.

Adding data from a new data source

You can select data from a data source that you have not used before. There are a number of types of data source available.

Do the following: 1. Click Add data.

2. Click Connect my data.

3. Select which type of data source to use.

4. Select which specific data source you want to add data from.

l File based data sources: Select a file. l Databases: Set which database to use. l Web files: Enter the URL of the web file.

l Other data sources: Specified by the connector to the database.

5. Select the tables and fields to load.

If you want to bypass the data profiling step, you can continue to step 8. 6. Click Profile.

The data will be profiled to provide field association recommendations.

7. If the data profiling resulted in recommendations, they are displayed, and you get assistance with validating and improving table relationships, for example, adding relationships, or removing relationships by renaming one or more fields.

Which data sources are available to me?

The data-source types that are available to you depends on a number of factors:

l Access settings

(15)

l Installed custom connectors

Qlik Sense contains built-in support for many data sources. To connect to additional data sources you may need a custom connector, supplied by Qlik or a third party. Custom connectors need to be installed before you can use them.

l Local files availability

Local files on your desktop computer are only available in Qlik Sense Desktop. They are not available on a server installation of Qlik Sense.

If you have local files that you want to load on a server installation of Qlik Sense, you need to transfer the files to a folder available to the Qlik Sense server, preferably a folder that is already defined as a folder data connection.

Selecting data fields from your data source

You can select which tables and fields to use when you add data, or when you edit a table. Some data sources, such as a CSV file, contain a single table, while other data sources, such as Microsoft Excel spreadsheets or databases can contain several tables.

If a table contains a header row, field names are usually automatically detected, but you may need to change theField names setting in some cases. You may also need to change other table options, for example Header size or Character set, to interpret the data correctly. Table options are different for different types of data sources.

Selecting data from a database

When adding data from a database, the data source can contain several tables. Do the following:

1. Select a Database from the drop-down list. 2. Select Owner of the database.

3. Select the first table to select data from. You can select all fields in the table by checking the box next to the table name.

4. Select the fields you want to load by checking the box next to each field you want to load.

You can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joint on common fields by default.

5. When you are done with your data selection you can continue in one of two ways:

l ClickProfile to continue with data profiling, and to see recommendations for table

relationships.

l ClickLoad and finish to load the selected data as it is, bypassing the data profiling step, and

to start creating visualizations. Tables will be linked using natural associations, that is, by commonly-named fields.

(16)

Selecting data from a Microsoft Excel spreadsheet

When adding data from a Microsoft Excel spreadsheet, it can contain several sheets. Each sheet is loaded as a separate table. An exception is if the sheet has the same field/column structure as another sheet or loaded table, in which case, the tables are concatenated.

Do the following:

1. Make sure you have the appropriate settings for the sheet:

Field names Set to specify if the table contains Embedded field names or No field names. Header size Set to the number of lines to omit as table header.

2. Select the first sheet to select data from. You can select all fields in a sheet by checking the box next to the sheet name.

3. Select the fields you want to load by checking the box next to each field you want to load.

You can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joined by common fields by default.

4. When you are done with your data selection you can continue in one of two ways:

relationships.

Selecting data from a table file

You can add data from a large number of data files. Do the following:

1. Make sure that the appropriate file type is selected in File format.

2. Make sure you have the appropriate settings for the file. File settings are different for different file types.

3. Select the fields you want to load by checking the box next to each field you want to load. You can also select all fields in a file by checking the box next to the sheet name.

You can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joined by common fields by default.

4. When you are done with your data selection you can continue in two different ways:

(17)

Returning to the previous step (Add data)

You can return to the previous step when adding data. Do the following:

l Click

ê

to return to the previous step ofAdd data.

Choosing settings for file types

Delimited table files

These settings are validated for delimited table files, containing a single table where each record is separated by a line feed, and each field is separated with a delimited character, for example a CSV file.

File format settings

File format Set toDelimited or Fixed record.

When you make a selection, the select data dialog will adapt to the file format you selected .

Field names Set to specify if the table contains Embedded field names or No field names. Delimiter Set theDelimiter character used in your table file.

Quoting Set to specify how to handle quotes: None = quote characters are not accepted

Standard = standard quoting (quotes can be used as first and last characters of a field value)

MSQ = modern-style quoting (allowing multi-line content in fields) Header size Set the number of lines to omit as table header.

Character set Set character set used in the table file.

Comment Data files can contain comments between records, denoted by starting a line with one or more special characters, for example //.

Specify one or more characters to denote a comment line. Qlik Sense does not load lines starting with the character(s) specified here.

Ignore EOF Select Ignore EOF if your data contains end-of-file characters as part of the field value.

Fixed record data files

Fixed record data files contain a single table in which each record (row of data) contains a number of columns with a fixed field size, usually padded with spaces or tab characters.

(18)

Setting field break positions

You can set the field break positions in two different ways:

l Manually, enter the field break positions separated by commas inField break positions. Each

position marks the start of a field. Example: 1,12,24

l EnableField breaks to edit field break positions interactively in the field data preview. Field break

positions is updated with the selected positions. You can:

l Click in the field data preview to insert a field break. l Click on a field break to delete it.

l Drag a field break to move it.

File format settings

Field names Set to specify if the table contains Embedded field names or No field names. Header size SetHeader size to the number of lines to omit as table header.

Character set Set to the character set used in the table file.

Tab size Set to the number of spaces that one tab character represents in the table file. Record line size Set to the number of lines that one record spans in the table file. Default is 1.

HTML files

HTML files can contain several tables. Qlik Sense interprets all elements with a <TABLE> tag as a table. File format settings

Field names Set to specify if the table contains Embedded field names or No field names. Character set Set the character set used in the table file.

XML files

You can load data that is stored in XML format. There are no specific file format settings for XML files.

QVD files

You can load data that is stored in QVD format. QVD is a native Qlik format and can only be written to and read by Qlik Sense or QlikView. The file format is optimized for speed when reading data from a Qlik Sense script but it is still very compact.

There are no specific file format settings for QVD files.

KML files

You can load map files that are stored in KML format, to use in map visualizations. There are no specific file format settings for KML files.

(19)

2.8 Adding data from Qlik DataMarket

You can add data from external sources with Qlik DataMarket. Qlik DataMarket offers an extensive collection of up-to-date and ready-to-use data from external sources accessible directly from within Qlik Sense. Qlik DataMarket provides current and historical weather and demographic data, currency exchange rates, as well as economic and societal data.

The major categories of Qlik DataMarket are:

l Business: The most relevant data on firms and establishments, both locally and nationwide. l Demographics: Detailed population data for North America, Europe and the rest of the world. l Weather: Current and historical weather conditions for major locations around the world. l Currency: Foreign exchange rates for major world currencies, updated daily.

l Society: Major socioeconomic data on wages, unemployment and poverty.

l Economy: Major economic indicators featuring data on GDP, prices, education and environment.

Some Qlik DataMarket data is available for free. Data in the Essentials package is available for a subscription fee.

The Essentials package is not available on Qlik Sense Desktop.

Qlik DataMarket data can be examined separately or integrated with your own data. Augmenting internal data with Qlik DataMarket can often lead to richer discoveries.

Qlik DataMarket data is current with the source from which it is derived. The frequency with which source data is updated varies. Weather and market data is typically updated at least once a day, while public population statistics are usually updated annually. Most macro-economic indicators, such as unemployment, price indexes and trade, are published monthly. All updates usually become available in Qlik DataMarket within the same day.

Data selections in Qlik Sense are persistent so that the latest available data is loaded from Qlik DataMarket whenever the data model is reloaded.

Most Qlik DataMarket data is global and country-specific. For example, world population data is available for 200+ countries and territories. In addition, Qlik DataMarket provides various data for states and regions within the United States and European countries.

Selecting data from Qlik DataMarket

Gaining access to Qlik DataMarket

Before you can use Qlik DataMarket data, you must accept the terms and conditions for its use. Also if you have paid for the Essentials package, you must enter your access credentials to use data in the package.

(20)

It is not necessary to accept Qlik DataMarket terms and conditions when using Qlik Sense Desktop. Access credentials for the DataMarket Essentials package are also not required because the Essentials package is not available on Qlik Sense Desktop.

Do the following:

1. Open the Qlik Management Console.

2. Select Qlik DataMarket under the License and tokens tab. 3. Select I accept the terms and conditions.

4. Select a Subscription, either Free or Essentials package. 5. If you select Essentials package, enter your access credentials:

Owner name Owner organization Serial number Control number

6. After entering your access credentials for the Essentials package, expand LEF access and click Get LEF and preview the license to download an LEF file from the Qlik Sense LEF server. Alternatively, you can copy the LEF information from an LEF file and past it in the text field.

Failed to get LEF from server is displayed if the serial number or control number is

incorrect.

7. Click Apply.

Selecting data from Qlik DataMarket

When selecting data from Qlik DataMarket, you select categories and then filter the fields of data available in those categories. The DataMarket categories contain large amounts of data, and filtering allows you to take subsets of the data and reduce the amount of data loaded.

Do the following: 1. Click Add data.

2. Click Qlik DataMarket in the Select a data source step to display the Qlik DataMarket data categories.

The major data categories are Business, Demographics, Weather, Currency, Society and Economy 3. Select a data category.

4. Select a subcategory of data in the Select a dataset step.

Clicking the

]

icon next to each subcategory description displays the metadata of the subcategory’s dataset.

(21)

Subcategories labeled Premium are part of the Essentials package. To select

Premium subcategories, you must have supplied your access credentials.

5. Select at least one filter from each field in the Select data to load step.

Each field is framed by a box. A field contains filters that enable you to filter the data according to your specific requirements and to limit the amount of data loaded. A field can contain a single set of filters, or it can contain multiple hierarchical columns of filters. For example, in the following data set, the Country field contains a single set of filters and the Sex field contains a hierarchy of two columns.

Selecting the field name (for example, Country) causes the entire block of filters to be selected. As you make selections, the table below the fields updates to reflect the accumulated selections.

The Qlik DataMarket field names cannot be changed in Add data the way field names from other sources can be changed. They can, however, be changed when creating or changing associations in the Profile step.

6. Click Profile when you are finished making your selections if you want to check the structure of the data when it is loaded and get recommendations for improving the data associations.

Integrating Qlik DataMarket with data from other sources

Qlik Sense integrates Qlik DataMarket data as it is added withAdd data. As data is selected from multiple sources, such as files, databases and DataMarket, Qlik Sense detects associations between tables and incorporates them in the data model. Not all associations are valid, and for that reason, you should profile the data to evaluate the associations before loading.

Profiling data is the recommended way to make changes to associations. If field names need to be changed to create associations or remove synthetic keys, they can be changed in theAdd data profile step.

(22)

There are two additional ways to create associations outside the profiling step. You can useAdd data to change field names in data sets other than DataMarket‘s, or you can edit the data load script.

Change field names

Do the following:

1. Click

@

next of the name of the data set with a field name you want to change.

Do not select a Qlik DataMarket data set because names of DataMarket fields cannot be edited.

2. Change a field name by clicking on the existing name in the column header and typing the new name. The new name must match a field name in a Qlik DataMarket data set to form an association with that data. To break an association with DataMarket data, change the field so that it does not match the DataMarket measure name.

3. Click Profile to see association formed by the name change.

If you create a new association, Qlik Sense indicates how strong the association is and whether it is not a recommended association.

Edit the data load script

When you add data tables inData manager, data load script code is generated. You can view the script code in theAuto-generated section of data load editor. You can also choose to unlock and edit the generated script code, but in that case the data tables can no longer be managed inData manager.

Returning to the previous step (Add data)

l Click

ê

to return to the previous step ofAdd data.

2.9 Managing data table associations

You have the opportunity to fix association problems with the data files you want to load. Qlik Sense will associate tables based on common field names automatically, but there are cases where you need to adjust the association, for example:

l If you have loaded two fields containing the same data but with a different field name from two

different tables, it's probably a good idea to name the fields identically to relate the tables.

l If you have loaded two fields containing different data but with identical field names from two different

tables, you need to rename at least one of the fields to load them as separate fields.

Qlik Sense performs data profiling of the data you want to load to assist you in fixing the table association. Existing bad associations and potential good associations are highlighted, and you get assistance with selecting fields to associate, based on analysis of the data.

(23)

Viewing recommendations

Association recommendations are displayed in a list, and you can navigate between recommendations and warnings using the

˜

and

¯

buttons. If the list contains warnings, there are association issues that need to be resolved.

Two data sources contain a field with related data but with different

names

In this case, the two tables contain a common field that is named differently in the two tables.Current shows that the tables are not associated, butSuggestions shows that there are two fields that contain similar data, with a high matching score.

To create an association between the tables, you need to rename one or both fields to a common name. Do the following:

1. Select the field pair that you find most suitable. This is usually the pair with the highest score. 2. Select the field name you want to use, or enter a new custom field name.

The fields are now renamed to have the same name, and the tables will be associated when you have loaded data.

(24)

Two data sources contain fields with the same name but unrelated

data

In this case, data profiling has revealed that the two tables contain fields with unrelated data, but with the same name, indicated by a low matching score. The ID field in the Sales table could be a unique identifier for every order, while the ID field in the Stores table is the identifier of a store. If you would load the tables without resolving the issue, they would be associated which could result in a problematic data model. To ensure that the tables are not associated, you need to rename the field names to be different. Do the following:

1. Select No association. 2. Click Rename fields.

The fields are now renamed, by qualifying them with the table name, in this case Sales.ID and Stores.ID. The tables will not be associated when you have loaded data.

Two tables contain more than one common field

If two tables contain more than one common field that would create an association, Qlik Sense would create a synthetic key. If this is encountered during data profiling, you will get recommendations to either keep one of the fields as a key by renaming the other common fields, or breaking the table association.

Loading data

When you have resolved the suggestions, you can proceed to load data, including the data you just added, by clicking .Load and finish.

Bypassing recommendations

You can load the selected data as it is, bypassing the data profiling recommendations, and start creating visualizations.

(25)

l ClickLoad and finish without applying any of the data profiling recommendations changes.

Tables will be linked using natural associations, that is, by commonly named fields.

Returning to the previous step (Add data)

(26)

3 Loading data with the data load script

This introduction serves as a brief presentation of how you can load data into Qlik Sense to provide background for the topics in this section, presenting how to achieve basic data loading and transformation using the data load script.

Qlik Sense uses a data load script, which is managed in the data load editor, to connect to and retrieve data from various data sources. In the script, the fields and tables to load are specified. It is also possible to manipulate the data structure by using script statements and expressions.

During the data load, Qlik Sense identifies common fields from different tables (key fields) to associate the data. The resulting data structure of the data in the app can be monitored in the data model viewer. Changes to the data structure can be achieved by renaming fields to obtain different associations between tables. After the data has been loaded into Qlik Sense, it is stored in the app. The app is the heart of the program's functionality and it is characterized by the unrestricted manner in which data is associated, its large number of possible dimensions, its speed of analysis and its compact size. The app is held in RAM when it is open. Analysis in Qlik Sense always happens while the app is not directly connected to its data sources. So, to refresh the data, you need to reload the script.

3.1 Using the data load editor

This section describes how to use the data load editor to create or edit a data load script that can be used to load your data model into the app.

The data load script connects an app to a data source and loads data from the data source into the app. When you have loaded the data it is available to the app for analysis. When you want to create, edit and run a data load script you use the data load editor.

A script can be typed manually, or generated automatically. Complex script statements must, at least partially, be entered manually.

(27)

A Toolbar with the most frequently used commands for the data load editor: navigation menu, global menu,Save,

u

(debug) andLoad data

°

. The toolbar also displays the save and data load status of the app.

Data load editor toolbars (page 59)

B Under Data connections you can save shortcuts to the data sources (databases or remote files) you commonly use. This is also where you initiate selection of which data to load.

Connect to data sources (page 28)

C You can write and edit the script code in the text editor. Each script line is numbered and the script is color coded by syntax components. The text editor toolbar contains commands for Search and replace, Help mode, Undo, and Redo. The initial script already contains some pre-defined regional variable settings, for exampleSET ThousandSep=, that you generally do not need to edit.

Edit the data load script (page 51)

D Divide your script into sections to make it easier to read and maintain. The sections are executed from top to bottom.

Organizing the script code (page 52)

E Output displays all messages that are generated during script execution.

Quick start

If you want to load a file or tables from a database, you need to complete the following steps inData connections:

(28)

1. Create new connection linking to the data source ( if the data connection does not already exist). 2.

±

Select data from the connection.

When you have completed the select dialog withInsert script, you can select Load data to load the data model into your app.

For detailed reference regarding script functions and chart functions, see the Script Syntax and Chart Functions Guide.

Connect to data sources

Data connections in the data load editor provide a way to save shortcuts to the data sources you commonly use: databases, local files or remote files.Data connections lists the connections you have saved in alphabetical order. You can use the search/filter box to narrow the list down to connections with a certain name or type.

The following types of connections exist:

l Standard connectors:

l ODBC database connections. l OLE DB database connections.

l Folder connections that define a path for local or network file folders. l Web file connections used to select data from files located on a web URL. l Custom connectors:

With custom developed connectors you can connect to data sources that are not directly supported by Qlik Sense. Custom connectors are developed using the QVX SDK or supplied by Qlik or third-party developers. In a standard Qlik Sense installation you will not have any custom connectors available.

You can only see data connections that you own, or have been given access rights to read or update. Please contact your Qlik Sense system administrator to acquire access if required.

Creating a new data connection

Do the following:

1. Click Create new connection.

2. Select the type of data source you want to create from the drop-down list. The settings dialog, specific for the type of data source you selected, opens. 3. Enter the data source settings and click Create to create the data connection.

The data connection is now created with you as the default owner. If you want other users to be able to use the connection in a server installation, you need to edit the access rights of the connection in Qlik

(29)

The connection name will be appended with your user name and domain to ensure that it is unique.

If Create new connection is not displayed, it means you do not have access rights to add data connections. Please contact your Qlik Sense system administrator to acquire access if required.

Deleting a data connection

Do the following:

1. Click

E

on the data connection you want to delete. 2. Confirm that you want to delete the connection. The data connection is now deleted.

If

E

is not displayed, it means you do not have access rights to delete the data connection. Please contact your Qlik Sense system administrator to acquire access if required.

Editing a data connection

Do the following:

1. Click

@

on the data connection you want to edit.

2. Edit the data connection details. Connection details are specific to the type of connection. 3. Click Save.

The data connection is now updated.

If you edit the name of a data connection, you also need to edit all existing references (lib://) to the connection in the script, if you want to continue referring to the same connection.

If

@

is not displayed, it means you do not have access rights to update the data connection. Please contact your Qlik Sense system administrator if required.

Inserting a connect string

Connect strings are required forODBC, OLE DB and custom connections. Do the following:

l Click

Ø

on the connection for which you want to insert a connect string.

(30)

You can also insert a connect string by dragging a data connection and dropping it on the position in the script where you want to insert it.

Selecting data from a data connection

If you want to select data to load in your app, do the following:

1. Create new connection linking to the data source (if the data connection does not already exist). 2.

±

Select data from the connection.

Referring to a data connection in the script

You can use the data connection to refer to data sources in statements and functions in the script, typically where you want to refer to a file name with a path.

The syntax for referring to a file is'lib://(connection_name)/(file_name_including_path)'

Example 1: Loading a file from a folder data connection

This example loads the fileorders.csv from the location defined in the MyData data connection.

LOAD * FROM 'lib://MyData/orders.csv';

Example 2: Loading a file from a sub-folder

This example loads the fileCustomers/cust.txt from the DataSource data connection folder. Customers is a

sub-folder in the location defined in the MyData data connection.

LOAD * FROM 'lib://DataSource/Customers/cust.txt';

Example 3: Loading from a web file

This example loads a table from the PublicData web file data connection, which contains the link to the actual URL.

LOAD * FROM 'lib://PublicData' (html, table is @1);

Example 4: Loading from a database

This example loads the table Sales_data from the MyDataSource database connection.

LIB CONNECT TO 'MyDataSource'; LOAD *;

SQL SELECT * FROM `Sales_data`;

Where is the data connection stored?

Connections are stored using the Qlik Sense Repository Service.You can manage data connections with the Qlik Management Console in a Qlik Sense server deployment. The Qlik Management Console allows you to delete data connections, set access rights and perform other system administration tasks.

(31)

In Qlik Sense Desktop, all connections are saved in the app without encryption. This includes possible details about user name, password, and file path that you have entered when creating the connection. This means that all these details may be available in plain text if you share the app with another user. You need to consider this while designing an app for sharing.

ODBC data connections

You can create a data connection to select data from an ODBC data source that has already been created and configured in theODBC Data Source Administrator dialog in Windows Control Panel.

Creating a new ODBC data connection

Do the following:

1. Click Create new connection and select ODBC. TheCreate new connection (ODBC) dialog opens.

2. Select which data source to use from the list of available data sources, either User DSN or System DSN.

System DSN connections can be filtered according to 32-bit or 64-bit.

ForUser DSN sources you need to specify if a 32-bit driver is used with Use 32-bit connection. 3. Add Username and Password if required by the data source.

4. If you want to use a name different from the default DSN name, edit Name. 5. Click Create.

The connection is now added to theData connections, and you can connect to, and select data from the connected data source.

Editing an ODBC data connection

Do the following:

1. Click

@

on theODBC data connection you want to edit. TheEdit connection (ODBC) dialog opens.

2. You can edit the following properties:

Select which data source to use from the list of available data sources, eitherUser DSN or System DSN.

Username Password Name 3. Click Save.

(32)

The settings of the connection you created will not be automatically updated if the data source settings are changed. This means you need to be careful about storing user names and passwords, especially if you change settings between integrated Windows security and database login in the DSN.

Best practices when using ODBC data connections

Moving apps with ODBC data connections

If you move an app between Qlik Sense sites/Qlik Sense Desktop installations, data connections are included. If the app contains ODBC data connections, you need to make sure that the related ODBC data sources exist on the new deployment as well. The ODBC data sources need to be named and configured identically, and point to the same databases or files.

Security aspects when connecting to file based ODBC data connections

ODBC data connections using file based drivers will expose the path to the connected data file in the connection string. The path can be exposed when the connection is edited, in the data selection dialog, or in certain SQL queries.

If this is a concern, it is recommended to connect to the data file using a folder data connection if it is possible.

OLE DB data connections

You can create a data connection to select data from an OLE DB data source .

Creating a new OLE DB data connection

Do the following:

1. Click Create new connection and select OLE DB. 2. Select Provider from the list of available providers.

3. Type the name of the Data source to connect to. This can be a server name, or in some cases, the path to a database file. This depends on whichOLE DB provider you are using.

Example:

If you selected Microsoft Office 12.0 Access Database Engine OLE DB Provider, enter the file name of the Access database file, including the full file path:

C:\Users\{user}\Documents\Qlik\Sense\Apps\Tutorial source files\Sales.accdb

If connection to the data source fails, a warning message is displayed.

4. Select which type of credentials to use if required:

l Windows integrated security: With this option you use existing Windows credentials of the

(33)

l Specific user name and password: With this option you need to enter User name and

Password.

If the data source does not require credentials, leaveUser name and Password empty.

5. If you want to test the connection, click Load and then Select database... to use for establishing the data connection.

You are still able to use all other available databases of the data source when selecting data from the data connection.

6. If you want to use a name different to the default provider name, edit Name.

You cannot use the following characters in the connection name: \ / : * ? " ' < > |

7. Click Create.

The Create button is only enabled if connection details have been entered correctly, and the automatic connection test was successful.

The connection is now added to theData connections, and you can connect to, and select data from the connected OLE DB data source if the connection string is correctly entered.

Editing an OLE DB data connection

Do the following:

1. Click

@

on theOLE DB data connection you want to edit. TheEdit connection (OLE DB) dialog opens.

2. You can edit the following properties:

l Connection string (this contains references to the Provider and the Data source) l Username

l Password l Name

3. Click Save.

The connection is now updated.

Security aspects when connecting to file based OLE DB data connections

OLE DB data connections using file based drivers will expose the path to the connected data file in the connection string. The path can be exposed when the connection is edited, in the data selection dialog, or in certain SQL queries.

If this is a concern, it is recommended to connect to the data file using a folder data connection if it is possible.

(34)

Folder data connections

You can create a data connection to select data from files in a folder, either on a physical drive or a shared network drive.

On a Qlik Sense server installation, the folder needs to be accessible from the system running the Qlik Sense engine, that is, the user running the Qlik Sense service. If you connect to this system using another computer or touch device, you cannot connect to a folder on your device, unless it can be accessed from the system running the Qlik Sense engine.

Creating a new folder data connection

Do the following:

1. Click Create new connection and select Folder.

TheCreate new data connection (folder) dialog opens. When you install Qlik Sense, a working directory is created, namedC:\Users\{user}\Documents\Qlik\Sense\Apps. This is the default

directory selected in the dialog.

If the Folder option is not available, you do not have access right to add folder connections. Contact your Qlik Sense system administrator.

2. Enter the Path to the folder containing the data files. You can either:

l Select the folder

l Type a valid local path (example:C:\data\MyData\) l Type a UNC path (example:\\myserver\filedir\).

3. Enter the Name of the data connection you want to create. 4. Click Create.

The connection is now added to theData connections, and you can connect to, and select data from files in the connected folder.

Editing a folder data connection

Do the following:

1. Click

@

on the folder data connection you want to edit. TheEdit connection (folder) dialog opens.

If

@

is disabled, you do not have access right to edit folder connections. Contact your Qlik Sense system administrator.

2. You can edit the following properties: Path

Name 3. Click Save.

(35)

Web file data connections

You can create a data connection to select data from files residing on a web server, accessed by an URL address, typically in HTML or XML format.

Creating a new web file data connection

Do the following:

1. Click Create new connection and select Web file. TheSelect web file dialog opens.

2. Enter the URL to the web file.

3. Enter the Name of the data connection you want to create. 4. Click Create.

The connection is now added to theData connections, and you can connect to, and select data from the web file.

Editing a web file data connection

Do the following:

1. Click

@

on the web file data connection you want to edit. TheSelect web file dialog opens.

2. You can edit the following properties: URL

Name 3. Click Save.

The connection is now updated.

Loading data from files

Qlik Sense can read data from files in a variety of formats:

l Text files, where data in fields is separated by delimiters such as commas, tabs or semicolons

(comma-separated variable (CSV) files).

l HTML tables.

l Excel files (except password protected Excel files). l XML files.

l Qlik native QVD and QVX files. l Fixed record length files.

l DIF files (Data Interchange Format). DIF files can only be loaded with the data load editor).

In most cases, the first line in the file holds the field names. There are several ways to load data from files:

l Add data

(36)

l Loading data from a file by writing script code

Selecting data from a data connection

Instead of typing the statements manually in the data load editor, you can use theSelect data dialog to select data to load. Do the following:

1. Open the data load editor.

2. Create a Folder data connection, if you don't have one already. The data connection should point to the directory containing the data file you want to load.

3. Click

±

on the data connection to open the data selection dialog.

Now you can select data from the file and insert the script code required to load the data.

You can also use the ODBC interface to load a Microsoft Excel file as data source. In that case you need to create an ODBC data connection instead of a Folder data connection.

Loading data from a file by writing script code

Files are loaded using aLOAD statement in the script. LOAD statements can include the full set of script expressions.

To read in data from another Qlik Sense app, you can use aBinary statement.

Example:

directory c:\databases\common;

LOAD * from TABLE1.CSV (ansi, txt, delimiter is ',', embedded labels);

LOAD fieldx, fieldy from TABLE2.CSV (ansi, txt, delimiter is ',', embedded labels);

How to prepare Excel files for loading with Qlik Sense

If you want to load Microsoft Excel files into Qlik Sense, there are many functions you can use to transform and clean your data in the data load script, but it may be more convenient to prepare the source data directly in the Microsoft Excel spreadsheet file. This section provides a few tips to help you prepare your spreadsheet for loading it into Qlik Sense with minimal script coding required.

Use column headings

If you use column headings in Excel, they will automatically be used as field names if you selectEmbedded field names when selecting data in Qlik Sense. It is also recommended that you avoid line breaks in the labels, and put the header as the first line of the sheet.

Formatting your data

It is easier to load an Excel file into Qlik Sense if the content is arranged as raw data in a table. It is preferable to avoid the following:

l Aggregates, such as sums or counts. Aggregates can be defined and calculated in Qlik Sense. l Duplicate headers.

(37)

l Extra information that is not part of the data, such as comments. The best way is to have a column for

comments, that you can easily skip when loading the file in Qlik Sense.

l Cross-table data layout. If, for instance, you have one column per month, you should, instead, have a

column called “Month” and write the same data in 12 rows, one row per month. Then you can always view it in cross-table format in Qlik Sense.

l Intermediate headers, for example, a line saying “Department A” followed by the lines pertaining to

Department A. Instead, you should create a column called “Department” and fill it with the appropriate department names.

l Merged cells. List the cell value in every cell, instead.

l Blank cells where the value is implied by the previous value above. You need to fill in blanks where

there is a repeated value, to make every cell contain a data value. Use named areas

If you only want to read a part of a sheet, you can select an area of columns and rows and define it as a named area in Excel. Qlik Sense can load data from named areas, as well as from sheets.

Typically, you can define the raw data as a named area, and keeping all extra commentary and legends outside the named area. This will make it easier to load the data into Qlik Sense.

Remove password protection

Password protected files are not supported by Qlik Sense.

Loading data from databases

You can load data from commercial database systems into Qlik Sense through the following connectors:

l Standard connectors using the Microsoft ODBC interface or OLE DB. To use ODBC, you must

install a driver to support your DBMS and you must configure the database as an ODBC data source in theODBC Data Source Administrator in Windows Control Panel.

l Custom connectors, specifically developed to load data from a DBMS into Qlik Sense.

Loading data from an ODBC database

The easiest way to get started loading data from a database, such as Microsoft Access or any other database that can be accessed through an ODBC data source, is by using the data selection dialog in the data load editor.

To do this, do the following:

1. You need to have an ODBC data source for the database you want to access. This is configured in the ODBC Data Source Administrator in WindowsControl Panel. If you do not have one already, you need to add it and configure it, for example pointing to a Microsoft Access database.

2. Open the data load editor.

3. Create an ODBC data connection, pointing to the ODBC connection mentioned in step 1. 4. Click

±

on the data connection to open the data selection dialog.

(38)

ODBC

To access a DBMS (DataBase Management System) via ODBC, it is necessary to have installed an ODBC driver for the DBMS in question. The alternative is to export data from the database into a file that is readable to Qlik Sense.

Normally, some ODBC drivers are installed with the operating system. Additional drivers can be bought from software retailers, found on the Internet or delivered from the (DBMS) manufacturer. Some drivers are redistributed freely.

The ODBC interface described here is the interface on the client computer. If the plan is to use ODBC to access a multi-user relational database on a network server, additional DBMS software that allows a client to access the database on the server might be needed. Contact the DBMS supplier for more information on the software needed.

Adding ODBC drivers

An ODBC driver for your DBMS(DataBase Management System) must be installed for Qlik Sense to be able to access your database. Please refer to the documentation for the DBMS that you are using for further details.

64-bit and 32-bit versions of ODBC configuration

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open DataBase Connectivity (ODBC)Data Source Administrator tool (Odbcad32.exe):

l The 32-bit version of theOdbcad32.exe file is located in the %systemdrive%\Windows\SysWOW64

folder.

l The 64-bit version of theOdbcad32.exe file is located in the %systemdrive%\Windows\System32

folder.

Creating ODBC data sources

An ODBC data source must be created for the database you want to access. This can be done during the ODBC installation or at a later stage.

Before you start creating data sources, a decision must be made whether the data sources should be User DSN or System DSN (recommended). You can only reach user data sources with the correct user credentials. On a server installation, typically you need to create system data sources to be able to share the data sources with other users.

Do the following:

1. Open Odbcad32.exe.

2. Go to the tab System DSN to create a system data source. 3. Click Add.

TheCreate New Data Source dialog appears, showing a list of the ODBC drivers installed. 4. If the correct ODBC driver is listed, select it and click Finish.

(39)

A dialog specific to the selected database driver appears. 5. Name the data source and set the necessary parameters. 6. Click OK.

OLE DB

Qlik Sense supports the OLE DB(Object Linking and Embedding, Database) interface for connection to external data sources. A great number of external databases can be accessed via OLE DB.

Logic in databases

Several tables from a database application can be included simultaneously in the Qlik Sense logic. When a field exists in more than one table, the tables are logically linked through this key field.

When a value is selected, all values compatible with the selection(s) are displayed as optional. All other values are displayed as excluded.

If values from several fields are selected, a logical AND is assumed. If several values of the same field are selected, a logical OR is assumed. In some cases, selections within a field can be set to logical AND.

Select data to load

You can select which fields to load from files or database tables and which views of the data source you want by using the interactiveSelect data dialog. The dialog shows different selection or transformation options depending on the type of file or database you use as data source. As well as selecting fields, you can also rename fields in the dialog. When you have finished selecting fields, you can insert the generated script code to your script.

You openSelect data by clicking

±

on a data connection in the data load editor.

Selecting data from a database

In order to select data from a database, you start by clicking

±

on an ODBC or OLE DB data connection in the data load editor. This is where you select the fields to load from the database tables or views of the data source. You can select fields from several databases, tables and views in one session.

Selecting database

Do the following:

1. Select a Database from the drop-down list. 2. Select Owner of the database.

The list ofTables is populated with views and tables available in the selected database.

Selecting tables and views

The table list will include tables, views, synonyms, system tables and aliases from the database. If you want to select all fields of a table, do the following: