Troubleshooting - Loading data

This section describes problems that can occur when loading and modeling data in Qlik Sense.

A data connection stops working after SQL Server is restarted

Possible cause  

If you create a data connection to a SQL Server, and then restart the SQL Server, the data connection may stop working, and you are not able to select data. Qlik Sense has lost connection to the SQL Server and was not able to reconnect.

Proposed action  

Qlik Sense:

Do the following:

• Close the app, and open it again from the hub.

Qlik Sense Desktop:

Do the following:

1. Close all apps.
2. Restart Qlik Sense Desktop.

Synthetic keys warning when loading data

If you have loaded several files, you may receive a warning that synthetic keys have been created after loading the data.

Possible cause  

If two tables contain more than one common field, Qlik Sense creates a synthetic key to resolve the linking.

See: Synthetic keys

Proposed action  

In many cases, you do not need to do anything about synthetic keys if the linking is meaningful, but it is a good idea to review the data structure in the data model viewer.

Circular references warning when loading data

Possible cause  

If you have loaded more than two tables, the tables can be associated in such a way that there is more than one path of associations between two fields, causing a loop in the data structure.

Proposed action  

See: Understanding and solving circular references

Data selection problems with an OLE DB data source

Possible cause  

If you are not able to select data from an OLE DB data connection, you need to check how the connection is configured.

Proposed action  

Do the following:

1. Verify that the connection string is correctly designed.

2. Verify that you are using appropriate credentials to log on.

See: Connecting to data sources

Character set problems with non-ANSI encoded data files

You may experience problems with character encoding in non-ANSI encoded data files when using an ODBC data connection.

Possible cause  

ODBC data connections do not provide full capabilities for character set encoding.

Proposed action  

Do the following:

• If possible, import the data files using a folder data connection, which supports more options for handling character codes. This is probably the best option if you are loading a Microsoft Excel spreadsheet or a text data file.

Connector is not working

You are trying to create a data connection to a separately installed connector connector in the data load editor, but the connection fails, or an existing connection is labeled as unknown.

The connector is not properly installed

Possible cause  

The connector is not properly installed according to installation instructions. If an app uses a connector on a multi-node site, the connector needs to be installed on all nodes.

Proposed action  

Do the following:

• Verify that the connector is installed according to instructions on all nodes of the site.

Download and install connectors

The connector is not adapted for Qlik Sense

Possible cause  

QlikView connectors need to be adapted for Qlik Sense if you want to be able to select data.

Proposed action (if you developed the connector yourself with the QVX SDK)

Do the following:

• You need to adapt the connector for Qlik Sense with an interface to select data.

Qlik Sense adaptation

Proposed action (if the connector was supplied to you)

Do the following:

• Contact the connector supplier to acquire a Qlik Sense adapted connector.

Data load script is executed without error, but data is not loaded

The script is executed without syntax or load errors, but data is not loaded as expected. A general recommendation is to activate debug to step through the script and examine execution results, but here are some common causes of error.

See: Debug the data load script

A statement is not terminated with a semicolon

Possible cause  

You have forgotten to terminate a statement with a semicolon.

Proposed action  

Do the following:

• Terminate all statements with a semicolon.

Single quote character inside a string

Possible cause  

A string contains a single quote character in, for example, a SET variable statement.

Proposed action  

Do the following:

• If a string contains a single quote character, it needs to be escaped with an extra single quote.

See: Using quotation marks in the script

Columns are not lining up as expected when selecting data from a fixed record file

Possible cause  

The file uses tab characters to pad the columns. Typically, you will see that the field headings do not line up with the expected data if you select Field breaks in the select dialog.

In this case, the tab character is usually equivalent to a number of characters.

Proposed action  

Do the following:

1. Select No field names in Field names.
2. Select Field breaks.
3. Increase the setting of Tab size until you see the columns lining up with the header.
4. Insert field breaks by clicking at the appropriate column positions.
5. Select Data preview.
6. Select Embedded field names in Field names.

The columns are now lined up properly, and each field should have the correct field name.

Error message "Invalid path" when attaching a file

Possible cause  

The file name is too long. Qlik Sense only supports file names up to 171 characters.

Proposed action  

Rename the file to a name that contains less than 172 characters.

Errors when loading an app converted from a QlikView document

You may receive errors when reloading an app that was converted from a QlikView document due to differences between the two products.

Absolute file path references are used in the script

Possible cause  

The load script refers to files using absolute paths, which is not supported in Qlik Sense standard mode. Examples of error messages are "Invalid Path" and "LOAD statement only works with lib:// paths in this script mode".

Proposed action  

Do the following:

• Replace all file references with lib:// references to data connections in Qlik Sense.

See: Converting a QlikView document into a Qlik Sense app

Unsupported functions or statements are used in the script

Possible cause  

If you get a syntax error when running the script in the data load editor, it may be related to using QlikView script statements or functions that are not supported in Qlik Sense.

Proposed action  

Do the following:

• Remove the invalid statement or replace it with a valid one.

See: QlikView functions and statements not supported in Qlik Sense

Problems connecting to, and loading data from Microsoft Excel files through ODBC

Possible cause  

You may encounter problems when setting up an ODBC data connection to a Microsoft Excel file, or loading data from from Microsoft Excel files through an ODBC data connection. This is commonly due to issues with the ODBCDSN configuration in Windows, or problems with the associated ODBC drivers.

Proposed action  

Qlik Sense has native support for loading Microsoft Excel files. If possible, replace the ODBC data connection with a folder data connection that connects to the folder containing the Microsoft Excel files.

Attaching a file by dropping it in Add data does not work

You are trying to attach a file by dragging it from Windows Explorer and dropping it on the Add data Attach files dialog in Qlik Sense, but the file is not uploaded.

Possible cause  

The file is stored in a ZIP archive. It is not possible to attach individual files from a ZIP archive in Qlik Sense, even though the archive appears like a folder in Windows Explorer.

Proposed action  

Extract the files from the ZIP archive before attaching them.

Tables with common fields are not automatically associated by field name

You have added two or more tables using Add data. The tables have fields with a common field name, but they are not automatically associated.

Possible cause  

When you added the tables, you kept the default option to enable data profiling in the Add data dialog. This option auto-qualifies all field names that are common between tables. For example, if you add table A and table B with a common field F1 using this option, the field will be named F1 in table A, and B.F1 in table B. This means that the tables are not automatically associated.

Proposed action  

Open Data manager and select the Associations view. Now you can associate the tables based on data profiling recommendations.

When you have associated the tables, click Load data.

See: Managing data associations

Date fields are not recognized as date fields in sheet view

You have fields containing date or timestamp data, but they are not recognized as date fields in sheet view, that is, they are not indicated with G in the assets panel and other field lists.

Data profiling was disabled when the table was added

Possible cause  

When you added the tables, you disabled data profiling from ¥ beside the Add data button.

With this option, date and timestamp fields that are recognized will function correctly, but they are not indicated with G in the assets panel and other field lists, and expanded property fields are not available.

Proposed action  

Open Data manager and click Load data.

Now, all date and timestamp fields should be indicated with G in the assets panel of sheet view. If they are still not indicated with G, the field data is probably using a format that is not recognized as a date.

Date format was not recognized

Possible cause  

The input format of the date field was not recognized when the table was loaded. Usually, Qlik Sense recognizes date fields automatically, based on locale settings and common date formats, but in some cases you may need to specify the input format.

Proposed action  

Open Data manager and edit the table containing the field that was not recognized as a date. The field is most likely indicated with ⏪ as a general field. Change the field type to Date or Timestamp, with an input format that matches the field data.

See: Making sure a date or timestamp field is recognized correctly

Section access rules were changed but all data is visible

Section access rules were modified in the script, and the script was executed to load data with active data reduction, but all data is visible in the app.

Possible cause  

Section access rules are only applied when the app is opened. When executing the script, section access rules are not applied on the data in the same session, for example if you have a sheet open on another tab.

Proposed action  

Reopen the app to see the effect of the modified section access rules.

Data manager does not show tables in app that contains data

When opening an app created in a Qlik Sense version earlier than 3.0, Data manager shows no tables and a message is displayed that the app contains no data.

Possible cause  

The improved data model in Qlik Sense 3.0 and later requires a data reload to complete data profiling and preparation.

Proposed action  

Click Load data in Data manager. This requires that the app can access the data sources that are used in the app.

Data manager workflows are broken for all users creating apps on a server

Users get errors when trying to use Add data or Load data in Data manager, or when refreshing the app in the browser.

Possible cause  

The Data manager uses QVD files to cache loaded data. These files are deleted automatically when they are no longer used, but if a large number accumulate, or they become corrupted, they can cause errors.

Proposed action  

Delete the folder containing the QVD files. On a Qlik Sense server, the cache is located at:

C:\ProgramData\Qlik\Sense\Apps\DataPrepAppCache

On a Qlik Sense Desktop, the cache is located at:

C:\Users\<username>\Documents\Qlik\Sense\Apps\DataPrepAppCache

Running out of disk space

There are several reasons why a system may run low on disk space, and the data manager's method of caching loaded data in QVD files is one possible cause.

Proposed action  

Delete the folder containing the QVD files. On a Qlik Sense server, the cache is located at:

C:\ProgramData\Qlik\Sense\Apps\DataPrepAppCache

On a Qlik Sense Desktop, the cache is located at:

C:\Users\<username>\Documents\Qlik\Sense\Apps\DataPrepAppCache

Data load editor does not display the script

When the data load editor is opened, the content of the editor is blank, and the script cannot be edited.

Possible cause  

The script contains very complex constructions, for example, a large number of nested if statements.

Proposed action  

Open the data load editor in safe mode by adding /debug/dle_safe_mode to the URL. This will disable syntax highlighting and auto-complete functions, but you should be able to edit and save the script.