topic Re: QlikView Treating nvarchar2(255) as Numeric in QlikView

QlikView Treating nvarchar2(255) as Numeric

jaspal_icon — Fri, 22 Jan 2016 21:41:36 GMT

Hello ,

I need some expert answer, in Oracle there is field ZipCode which having data type as nVarChar2(255).

Select statement returns the ZipCode as 012345 but the select statement which creating QVD out of it gives only 12345.

So it removing leading 0, and treating this particular data type as numeric. is there any reason why? is this datatype is not getting interpreted by QlikView as string (any known bug).

I dont want to use casting or anything on query, as all my queries are like

Select *;

Store .....

Please suggest.

Thanks in advance.

Re: QlikView Treating nvarchar2(255) as Numeric

petter — Sat, 23 Jan 2016 01:14:51 GMT

QlikView was designed to interpret every value for every column from a flat file or a database as if the value is a number. If it looks like a number - it becomes a number - if it doesn't it will be kept as a text value. So fields in QlikView are actually not enforcing a single data type - it can be mixed. A field can contain both text, numbers, dates, booleans and dual values. Even for relational databases that have columns that are strictly one type QlikView will insist on trying to interpret each and every value. This is explained very thoroughly by hic Henric Cronström in this document Automatic Number Interpretation. It would have been very nice to be able to turn off this overly interpretive behavior with some kind of switch. But that does not exist.

It is not possible to enforce strict text from varchar/text columns in a relational database unless you do something that looks like typecasting:

LOAD

Text( ProductCode ) AS ProductCode,

.....

....

And it is quite "boring" and tedious to have to wrap every column into this Text() function to disable automatic number interpretation. For larger customer accounts I took the effort of designing a small routine to do this automatically so a SQL table could be read and the LOAD code would be generated automatically with Text() for every non-numeric character column. It is the only way of achieving what you ask for.

Re: QlikView Treating nvarchar2(255) as Numeric

Sat, 23 Jan 2016 15:05:06 GMT

You can also format it with the num() function.

I.e. : Num(FieldName,'0000000000') as NumFieldName..

Re: QlikView Treating nvarchar2(255) as Numeric

jaspal_icon — Tue, 26 Jan 2016 18:53:11 GMT

Thanks Petter for detailed information. It's going to be tedious not boring if there is no such switch exists.

Earlier we had trailing 0's which was coming due to QlikView 11 initial release version. After that we moved to 11.2 SR5 and SR12 now. Trailing 0's issues resolved by upgrading, but the preceding 0's is nothing to do with any newer release as I think so.

Still if any one from Qlik product team is going to answer this or any hot fix available by chance then it will be great.

Thanks

Re: QlikView Treating nvarchar2(255) as Numeric

petter — Wed, 27 Jan 2016 03:30:41 GMT

I guarantee ... there is no such switch. And as I explained the preceding 0's is not a bug it's a known behaviour and feature. hic‌ has specifically written about it. If you expect answers from someone at the product team the Community site is probably not the right place to wait for an answer.

Re: QlikView Treating nvarchar2(255) as Numeric

hic — Wed, 27 Jan 2016 06:46:22 GMT

I can confirm what Petter says: There is no such switch.

There could however exist a simple work-around... Try the following: Start by defining all your Zip codes in a temporary table, with the correct format. Then Load your data without changing your code. Your Zip codes should then inherit the defined format automatically, if the same field name is used. Finally, you should delete the temporary table. Hence:

Tmp:

Load Num(RecNo(),'000000') as Zip Autogenerate 999999;

Drop Table Tmp;

HIC

Re: QlikView Treating nvarchar2(255) as Numeric

lawrenceiow — Thu, 28 Jan 2016 10:31:21 GMT

If you do not have many fields of this type (you only mention ZipCode) you could modify your numerous load statements slightly by adding an extra field for the text version as per example given by petter-s

LOAD
Text( ZipCode ) AS ZipCodeText,*;

You could then DROP the numeric version, if necessary. At least that way you don't have to make too many changes to your script.