Qlikview Changes data?

[#1]

Hello,

I'm facing a very odd problem.

When I load some data from Oracle DB, one of the data was changed unintentionally.

The original data was "6132401E2055" ,VARCHAR2(12) and trimed it in SQL using "substr(data,1,9)"

so the result should be "6132401E2" but in QVW, it became "6132401D2"

Is somebody able to explain this?

I'm really confusing.

※For your information,

when I devided the data to substr(data,1,7) as A and substr(data,8,2) as B

and combine it inside the TEXT like "TEXT(A&B)",

the result was "6132401E2", as it should be

※Qlikview version is 11.20 SR3

Thank you in advance.

[Gysbert_Wassenaar]

Qlikview tries to interpret strings as numbers. The E is interpreted as an indicative of an exponential notation. You'll have to use the text() function to force Qlikview to load the data as a text value and don't try to make a number out of it.

[its_anandrjs]

Hi,

Try with Text function in qlikview when reading this data means loading into qlikview text with field name

Load

FieldName,

Text(FieldName) as NewField

From Location;

Regards

Anand

[#4]

Thank you for your advice, Cysbert.

I tried TEXT function.

when I devided the data to substr(data,1,7) as A and substr(data,8,2) as B

and combine it inside the TEXT like "TEXT(A&B)", it worked.

but substr(data,1,9) as C and convert it "TEXT(C)" , it didin't work.

[#5]

Thank you Anand,

Unfortunately, it didin't work. as I mentioned in the comment for Gysbert.

[ychaitanya]

Why dont you use Left(Cloumunname,9)  rather than substring..?

Hope that helps...!

Thanks

Chaitanya

[jonathandienst]

Hi

Load it with text(data) before you start manipulating it, otherwise you are manipulating the number which QV read, rather than the original text. If you want the first 9 characters:

     Left(Text(data), 9) ....

HTH

Jonathan