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
to
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)
