Troubleshooting: Error: QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: S1000, ErrorCode: 50310, ErrorMsg: [Qlik][Support] (50310) Unrecognized ICU conversion error.

Anonymous — Thu, 12 Jul 2018 21:49:43 GMT

All,

Quick Background

I wanted to share troubleshooting this issue when we had a QLIK Data Load.

We try to do the QVD extract/transform pull and generate so other apps can use them.

Problem

During one of the QVD regen load scripts, an error was thrown:

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: S1000, ErrorCode: 50310, ErrorMsg: [Qlik][Support] (50310) Unrecognized ICU conversion error.

Search on Problem Results

Looking at other sources, it pointed to odd characters in the data.

A list of these (QLIK community items) can be found at the bottom of the post titled "Other Resources".

Please note that this method may have issues and there maybe more efficient and effective ways of doing it.

This is to help get the debugging / resolution going.

Identify Trouble Data

The first thing was to identify what data was causing it to abort.

The script that was aborting was generic:

TABLE_IN_QUESTION:

LOAD *;

SQL

SELECT *

FROM "BICORE"."TABLE_IN_QUESTION";

Store TABLE_IN_QUESTIONinto [lib://Qlik Data/QVD/Extract/TABLE_IN_QUESTION].qvd;

Drop Table TABLE_IN_QUESTION;

We copied this into another QLIK app so we could focus solely on this part.

So we did a query to get the data divided up by a major milestone (e.g. years).

When we did this, we had a span of data from 2007-2018.

So the first thing added was a where clause to break it up (starting point was (max-min)/2 approx.):

TABLE_IN_QUESTION:

LOAD *;

SQL

SELECT *

FROM "SCHEMA"."TABLE_IN_QUESTION"

WHERE

YEAR <= '2010';

It was ok, so increment by 1 or 2 (your choice) it until it bombed (2017 in this case).

Next we looked at the year in question to see what was going on.

Identify Trouble Data Structure

The script was modified from

SELECT * FROM

SELECT

column_1,

column_2,

......

column_10,

......

column_n

FROM

where

YEAR = '2017';

where column_i were the actual column names.

At the n/2 point, we added a /* and at the end before the from added the last */.

It passed. (so these were not the issue columns)

So move the n/2 point up (by a few or by 1, whatever).

At one point it will fail.

When it failed, we realized this is the column that was having the issue.

The characters present were not in the normal range (ansi/utf8) and this was confirmed by the oracle query:

select

from

TABLE_IN_QUESTION

WHERE

length(asciistr(WHATEVERYOURCOLUMNISCALLED))!=length(WHATEVERYOURCOLUMNISCALLED)

where the WHATEVERYOURCOLUMNISCALLED is the column that was having the issue.

Possible Resolutions

At this point, the question is what to do to resolve it:

1 - the data may need to be modified

2 - a function applied to strip them out

3 - the column can be left out

4 - there maybe some ODBC things that can be done

For now, we are going to try option 2 (option 1 was out, option 3 had dependencies, option 4 not considered at this point)

REGEXP_REPLACE(WHATEVERYOURCOLUMNISCALLED, '[^a-zA-Z0-9]+', '') WHATEVERYOURCOLUMNISCALLED

This strips out the offending parts. Please note we have not tested this with other characters to see how it behaves. Important for international data sets.

This also assigns an alias back to itself so other apps that may use it can remain untouched.

Side note

Data pull raw from Oracle it had this issue.

From a text file was able to load without the issue.

Conclusion

We went with a function (oracle supplied, will try to not have it as a manual one unless we have to) to try to solve the issue for now.

Hopefully this has helped some in debugging and figuring out

Other Resources

Other Documents on the QLIK site on the issue and how they resolved them.

QVX_UNEXPECTED_END_OF_DATA: SQL##f - (50310) Unrecognized ICU conversion error.

https://community.qlik.com/thread/286582

ErrorCode: 50310 loading from ODBC connected database

https://community.qlik.com/thread/266128

QVX_UNEXPECTED_END_OF_DATA:

https://community.qlik.com/thread/294175

QVX_UNEXPECTED_END_OF_DATA: SQL##f - (50310) Unrecognized ICU conversion error.

https://community.qlik.com/message/1410145#1410145

Oracle Query used in identification of the odd data:

In the where clause:

Oracle part used on different data

length(asciistr(WHATEVERYOURCOLUMNISCALLED))!=length(WHATEVERYOURCOLUMNISCALLED)

topic Troubleshooting: Error: QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: S1000, ErrorCode: 50310, ErrorMsg: [Qlik][Support] (50310) Unrecognized ICU conversion error. in App Development

Troubleshooting: Error: QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: S1000, ErrorCode: 50310, ErrorMsg: [Qlik][Support] (50310) Unrecognized ICU conversion error.