Prior to upgrading to the April 2018 release, VARCHAR2 data types in our Oracle database were being loaded as text. After upgrading, those data types are now loading as numbers. This causes problems for our incrementally-loaded QVDs as any data loaded and stored prior to the upgrade displays as text in a given field, while any new data displays as a number, even if the same value.
For example, a [Fiscal Year] field will now look like this in Qlik Sense:
The same Fiscal Year displays as two distinct entries in a Filter Pane, once as text and once as a number.
To test this, I executed the exact same query in a load editor on our Production server (November 2017) and our Test server (April 2018): select fiscal_year from mft_fiscal_period;
fiscal_year is a varchar2 data type.
QlikProd displays fiscal_year as text, as it should. QlikTest displays fiscal_year as a number, despite not being asked to do so.
This will not do.
Is there any way to force Qlik to not alter the underlying data type?
Thank you. I am quite familiar with the text() function, but it won't solve our problem.
Our QVDs are not generated with load statements or references to individual columns. They are generated with "select * from [insert_table_name_here]" statements inside a script that loops through all of our Oracle tables.
Even if there were explicit references to column names, text() would not address the incrementally-loaded QVDs now rendered completely useless by the April 2018 release. The previously-loaded data was stored as text. Newly-loaded incremental data is stored as numbers. There is now a mix of numbers and strings in the same field, despite no change whatsoever to the script that generates the QVDs. As my example above illustrates, that means [Fiscal Year] fields (and many others) no longer have a single entry in a Filter Pane for the same value. 2018 shows up once as text and once as a number.
I am looking for a way to force Qlik to treat text as text, and it seems quite odd that Qlik would, by default, convert a data type absent a specific request to do so.
Ok, I already had this problem the solution I found was to give a load after loading the select follows idea,
Text (Year) AS Year;
Select * From Table
That would work fine if loading a single table. We are loading hundreds with a single script:
for table_name = first_table to last_table;
select * from [table_name];
[Fiscal Year] is not the only field suffering from this problem. There are many. And [Fiscal Year] is not in every table.
text() is not a feasible workaround.
Thanks, Marcus, it helps to know I'm not the only one. And I agree that if this is by-design behavior, it is a bug. I was already considering the same solution mentioned in that thread - altering the load scripts to scan all columns for the source data type and force-feeding those into Qlik. That seems like a completely unnecessary (and labor-instensive; and performance-degrading) step.