Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

rogeriogyn
New Contributor III

Load Script BUG - auto convert Text to Number

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
0606110047Prooduto 1301100
0606110047Prooduto 2301100
0606110050Prooduto 330900

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

Labels (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Load Script BUG - auto convert Text to Number

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?

11 Replies
MVP
MVP

Re: Load Script BUG - auto convert Text to Number

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

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

- Marcus

MVP
MVP

Re: Load Script BUG - auto convert Text to Number

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
New Contributor III

Re: Load Script BUG - auto convert Text to Number

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

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

vlad_komarov
Valued Contributor III

Re: Load Script BUG - auto convert Text to Number

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

92334dc373e0487c999e3b521be03f46.png

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

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

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

MVP
MVP

Re: Load Script BUG - auto convert Text to Number

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.

Community Browser