Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
Thank you Anand,
Unfortunately, it didin't work. as I mentioned in the comment for Gysbert.
Why dont you use Left(Cloumunname,9) rather than substring..?
Hope that helps...!
Thanks
Chaitanya
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