topic Re: Load Script BUG - auto convert Text to Number in QlikView

Load Script BUG - auto convert Text to Number

rogeriogyn — Mon, 26 Jan 2026 18:19:17 GMT

Hello guys,

Take a look at the simple script below:

TAB1:
LOAD * Inline [
CodProd,Prod
0606110047,Prooduto 1
R606110047,Prooduto 2
0606110050,Prooduto 3
];


TAB2:
Load * inline [
CodProd,Qtde,Valor
0606110047,10,500
R606110047,20,600
0606110050,30,900
];

And The Result is:

CodProd	Prod	sum(Qtde)	sum(Valor)
		60	2000
0606110047	Prooduto 1	30	1100
0606110047	Prooduto 2	30	1100
0606110050	Prooduto 3	30	900

The text field value 'R606110047' is auto conveted to '0606110047'.

This is obviously a bug.

And yes, there's a way to fix it. Is to use Text(CodProd).

But we use a qvw to extract, another to transform, and another to show the chart. And I would have to do this in all the qvw files.

And also the field CodProd is used in almost every table.

Another anoying thing, is that we manage all the extract tables within a xls file. And in order to use text(<field>) we have to remove from the excel file and put it direct in the load script. By doing so, we lost our pattern on extract.

Is there a Release that fix this? Is this a known bug? Will it be fixed?

I'm using win7, and Qlikview Desktop 11.20 SR12 64bits

Thanks

Re: Load Script BUG - auto convert Text to Number

swuehl — Thu, 17 Dec 2015 15:45:25 GMT

I can't reproduce this on QV11.20 SR11 64 bits, Win7 with the INLINE LOAD tables.

What are your default format settings set in the script?

Re: Load Script BUG - auto convert Text to Number

marcus_sommer — Thu, 17 Dec 2015 16:31:09 GMT

I too can't reproduce this on QV11 SR12 64-bit.

- Marcus

Re: Load Script BUG - auto convert Text to Number

swuehl — Thu, 17 Dec 2015 16:36:32 GMT

I assume that this is caused by a default format applying, maybe money format?

I am not sure if you would need to change every QVW, or only the one where you initially load the data.

I am also not sure about your issue with the xls file (I just haven't fully understood your setting).

Could you detail this part a bit?

Re: Load Script BUG - auto convert Text to Number

rogeriogyn — Thu, 17 Dec 2015 16:54:02 GMT

That's exactly that, money format is 'R$ #.##0,00;-R$ #.##0,00' --> (Brazilian REAL)

If I change, it works fine.

I'm sending the qvw !

Anyway, one thing should not interfere with another, right ?

Re: Load Script BUG - auto convert Text to Number

Peter_Cammaert — Thu, 17 Dec 2015 16:58:45 GMT

If you change the initial R into another character (like a Z or A), does it work then?

Re: Load Script BUG - auto convert Text to Number

vlad_komarov — Thu, 17 Dec 2015 17:13:04 GMT

Rogerio,

Issue with leading zeros is actually a well-known QV issue: Leading 0 in Text field Qlikview transforms data incorrectly

Back to your problem: I was not able to reproduce it either (using Q-Sense 2.1), so it will be interesting to see what version of Qlik do you have and what regional (money, numeric and others) setting are you using...

But if this issue is reproducible on your side, here are some notes:

I am not 100% sure how the "format auto-detection" works in QV, but I am assuming that they are detecting the field's type (TEXT/NUM/DATE, etc) by the first record in the table during the load.

In your case it's numeric and system forces other records into numeric format and it's probably why the R606110047 is getting converted to 0606110047.

Not much else you can do but convert the Prod field to text using text(Prod)..

Regards,

Vladimir

Re: Load Script BUG - auto convert Text to Number

Peter_Cammaert — Thu, 17 Dec 2015 17:18:54 GMT

Well that depends if your name is QlikView or not. To QlikView, everything that matches a number format pattern is both a number and a string representation (and that includes dates and timestamps as well). QlikView remembers only one string representation for every number. Therefor the Rnnnn thing will be lost as there is already another string representation for that number (the one first read by QV).

If you do not treat Product IDs as real Product IDs (by using the text() function), you'll get into trouble again with these two product IDs: 000000000654 and 654. Different product IDs, right? Not for QlikView.

Block QlikView from doing its best in recognising numbers. Use the text() function to store Product IDs as strings. .

Best,

Peter

Re: Load Script BUG - auto convert Text to Number

Peter_Cammaert — Thu, 17 Dec 2015 17:24:23 GMT

In QlikView, every field value can have a different type. Nobody forces entire columns to be of the same type.

Re: Load Script BUG - auto convert Text to Number

swuehl — Thu, 17 Dec 2015 17:27:46 GMT

Kind of strange that it converts your text to number using the money format, though the format doesn't seem to exactely match. I would personally consider this as a bug.

But besides that, I think Peter is right, if you want to consider these values as distinct product IDs, I believe you should use pure text values for them, using text() to avoid number interpretation.

Re: Load Script BUG - auto convert Text to Number

Peter_Cammaert — Thu, 17 Dec 2015 17:49:22 GMT

I consider this a bug too, but there is probably a WAD-like reason to explain this weird behavior.

In the meantime, I'll try to make everyone even more depressed This

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

RawData:

LOAD * INLINE [

ID Spec

1 006060417

2 R006060417

3 R$006060417

4 R$ 006060417

5 R$ 006060417,89

6 R$006060417,89

7 R006060417,89

8 006060417,89

](delimiter is '\t');

produces this as straight table output:

Money format strings definitely have a license to freewheel...

Re: Load Script BUG - auto convert Text to Number

rogeriogyn — Thu, 17 Dec 2015 19:06:53 GMT

The problem is not the '0' before, is really the money format.

I lost a lot of time with this issue.

I also still think this is a bug.

Anyway, I will change the money format, thats easier.

Thanks all for your time.