topic Re: Qlikview Changes data? in QlikView

Qlikview Changes data?

Tue, 11 Nov 2014 09:08:08 GMT

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.

Re: Qlikview Changes data?

Gysbert_Wassenaar — Tue, 11 Nov 2014 09:24:09 GMT

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.

Re: Qlikview Changes data?

its_anandrjs — Tue, 11 Nov 2014 09:25:07 GMT

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

Re: Qlikview Changes data?

Tue, 11 Nov 2014 09:31:33 GMT

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.

Re: Qlikview Changes data?

Tue, 11 Nov 2014 09:33:29 GMT

Thank you Anand,

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

Re: Qlikview Changes data?

ychaitanya — Tue, 11 Nov 2014 09:37:33 GMT

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

Hope that helps...!

Thanks

Chaitanya

Re: Qlikview Changes data?

jonathandienst — Tue, 11 Nov 2014 09:46:07 GMT

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