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?
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 ?
BUG-R.qvw 149.5 K
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. .
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.
for some additional information.
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';
LOAD * INLINE [
4 R$ 006060417
5 R$ 006060417,89
](delimiter is '\t');
produces this as straight table output:
Money format strings definitely have a license to freewheel...
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)..