SQL Select statement is not loading the expected data in to QlikView.
The symptom can for example be;
- Data records are missing in QlikView
- More data than expected is loaded into QlikView
- No data is loaded into QlikView
- Reload fails with an database error code
The limitation in this scenario is that ODBC or OLEDB is used to access the data source.
For custom connectors the scenario might be a bit different.
Troubleshooting a failed connection or an unexpected results from SQL queries can be done by confirming that; the connection is valid, the SQL query is valid and that the loaded data structure is valid in QlikView.QlikView connection detailsIt is very important that any troubleshooting is done with the same connection and credentials as in QlikView
Reference connectionAn initial troubleshooting procedure is to make sure that the user can access the database. If any of the steps below fails, then there is a generic access issue to the database. Unless this is resolved, the connection will not work from QlikView either.
- Which Windows user is performing the reload?
- What database driver is used for the connection?
- What user credentials are used for the connection?
- Which DSN is used for the connection? (ODBC connections only)
Reference queryWith the connection confirmed as functional it is time to validate that the query which fails in QlikView.
- Log in to Windows with the same user account that observed the issue
- Open reference database client, for example from the database vendor
- Connect the client to the database with exactly the same driver and user credentials as in the QlikView application
- Make a generic query to the database to validate that the connection is open and that data can be received.
Data structureQlikView requires that loaded data is a two dimensional flat table
- Execute exactly the same SQL query as in QlikView.
NOTE: The SQL key word in QlikView is not part of the actual query, the query is the part following the key word.
- Confirm that the returned resultset has the expected number of fields and field names
- Confirm that the returned resultset has the expected number of rows
- Confirm that the returned resultset has the expected data content and data formats
- Confirm that the loaded data is two dimensional, i.e. fields and rows
- Confirm that the returned result is one table. QlikView only handles one table per load statement.