Load Script BUG - auto convert Text to Number

[rogeriogyn]

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

[swuehl]

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?

[marcus_sommer]

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

- Marcus

[swuehl · Accepted solution]

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?

[rogeriogyn]

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 ?

[Peter_Cammaert]

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

[vlad_komarov]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[swuehl]

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.

See also

Automatic Number Interpretation

for some additional information.