VARCHAR2 as number as of April 2018

[flytrapper]

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:

2015

2016

2017

2018

                 2018

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?

[giovanneb]

In the data load, treat the Year field as text

Load

text (year) AS Year

Best Regards

[flytrapper]

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.

[giovanneb]

Ok, I already had this problem the solution I found was to give a load after loading the select follows idea,

Load

*,

Text (Year) AS Year;

Select * From Table

[flytrapper]

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];

  next 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.

[giovanneb]

Okay, this is a big problem.

[marcus_sommer]

Maybe this is helpful for you in any way: Re: Load from QVD gives different values compared to source db table

- Marcus

[flytrapper]

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.

[dionverbeke]

did you try to add SET VERBATIM = 1; // Edited

[flytrapper]

I have "set verbatim = 1;" at the top of all my scripts all along. should the syntax be "set verbatim = true;" ?