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

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

Mon, 21 Apr 2014 09:39:43 GMT

Hi folks,

Please have a look at the image below:

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,

Re: Integer field after extraction from MySQL DB corrupted.

anbu1984 — Mon, 21 Apr 2014 09:55:28 GMT

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;

Re: Integer field after extraction from MySQL DB corrupted.

anil2185 — Mon, 21 Apr 2014 10:05:38 GMT

Hi,

You can use round() function.

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

Regards,

Anil

Re: Integer field after extraction from MySQL DB corrupted.

Tue, 22 Apr 2014 01:34:18 GMT

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,

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

Tue, 22 Apr 2014 01:46:16 GMT

Hi Anil,

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

Regards,

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

Tue, 22 Apr 2014 03:00:34 GMT

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->'###########################'

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

jagan — Tue, 22 Apr 2014 03:24:43 GMT

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.

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

Tue, 22 Apr 2014 03:34:18 GMT

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,

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

Tue, 22 Apr 2014 03:40:15 GMT

Hi Wenkun,

Can't.

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

jagan — Tue, 22 Apr 2014 03:49:52 GMT

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.

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

Colin-Albert — Tue, 22 Apr 2014 06:16:11 GMT

Have a look at this thread

number of 17 digits

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

Tue, 22 Apr 2014 06:30:24 GMT

Thanks Colin.

Regards,

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

jagan — Tue, 22 Apr 2014 10:21:06 GMT

Hi,

If you are not using for calculation then try

LOAD

Text(Number) AS Number_Text,

FROM Datasource;

Regards,

Jagan.

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

Fri, 25 Apr 2014 04:14:37 GMT

Thanks Jagan.

Warm Regards,

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

jagan — Fri, 25 Apr 2014 04:21:11 GMT

Hi,

Can you close this discussion.

Regards,

Jagan.