Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi folks,

Please have a look at the image below:

NumFieldIssue.jpg

After trying to to extract a table from MySQL db, one of the data fields got corrupted. Its an ID field, tried several methods to fix it but failed to solve the issue --> Num(), floor(), int() functions where used. Please advise. Thanks in advance.

Best Regards,

R

1 Solution

Accepted Solutions
MVP
MVP

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi,

If you are not using for calculation then try

LOAD

Text(Number) AS Number_Text,

*

FROM Datasource;

Regards,

Jagan.

14 Replies
anbu1984
Honored Contributor III

Re: Integer field after extraction from MySQL DB corrupted.

Data fields are not corrupted. Large numbers are shown in exponential form. To convert to decimal numbers, first cast it to Decimal long enough to hold your integer and then cast it to character

SELECT CAST(CAST(b AS DECIMAL(30)) AS CHAR) FROM abcd;

anil2185
New Contributor III

Re: Integer field after extraction from MySQL DB corrupted.

Hi,

You can use round() function.

Syntax :- Select round(field_name) as ID from table;

Regards,

Anil

Not applicable

Re: Integer field after extraction from MySQL DB corrupted.

Hi Anbu,

Apologize for the misinterpretation, I don't want to convert it to decimal number - I want to extract them as it is as a long integer. Any clue how to do so?

Best Regards,

R

Not applicable

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi Anil,

Doesn't seem to work, any other idea on how to go bout doing it?

Regards,

R

Not applicable

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

I think the column didn't get corrupted. Just because your number is too big, it just show like that. It's still integer. Please note, whether the text align right (int) or left (text).

If you insist to display it like integer, you can go to properties->number->integer->'###########################'


MVP
MVP

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi Ram,

How many digits are there in this number field.  Try like this

Data:

LOAD

*,

Num(Value)  AS New Value;

SELECT

*

FROM Table;

If it not works then divide the number by Millions / Thousands.

Regards,

Jagan.

Not applicable

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi Jagan,

21388881293400499 - sample value from the field. If am not mistaken 17-digits integer. Am going to try out the script which you have posted, see if it works out.

Thanks a bunch.

Best Regards,

R

Not applicable

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi Wenkun,

Can't.

MVP
MVP

Re: Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

Hi,

For 14 digits the number is displaying as such, but when it exceeds the it is showing e.

=Num(21388881293400499/1000, '###.#0')

Try this.

Regards,

Jagan.

Community Browser