topic Re: number of 17 digits in QlikView

number of 17 digits

ali_hijazi — Wed, 11 Sep 2013 17:26:42 GMT

hello I'm reading data from an AS400 database

there is a field that contains account numbers composed of at most 17 digits

when I display these values I see something like the following:

1.5688946100202e+016

9009342456

1.5688946100202e+015

1.5688946100202e+014

what I want is to display the number as is if if its originally 17 digits and when it is composed of less than 17 digits I want to add leading zeroes

I used the following function

num(field,'00000000000000000')

the first number returned blank and the rest were well transformed

please advise of what to do

Re: number of 17 digits

Gysbert_Wassenaar — Wed, 11 Sep 2013 17:44:50 GMT

I don't think numbers that large can be displayed as only digits. You can try making a dual value from them, but that will add a lot of overhead memory wise. See attached example.

Re: number of 17 digits

rajeshvaswani77 — Wed, 11 Sep 2013 17:45:58 GMT

Hi Ali,

What was the first number value that returned blank?

thanks,

Rajesh Vaswani

Re: number of 17 digits

hic — Thu, 12 Sep 2013 17:44:10 GMT

As Gysbert says, such a large number cannot be displayed as an integer. The 64-bit IEEE float can only show 14 digits. Instead, you should try

Repeat('0',17-Len(Text(Field))) & Text(Field) as Field2

HIC

Re: number of 17 digits

ali_hijazi — Fri, 13 Sep 2013 12:23:02 GMT

ok I'm reading from an AS400 database this field is numeric and contains the account ids I simply load * from that table and I get the following account number displayed as such in qlikview: 1.5688946100202e+016 how can I read display the 17 digits of this number knowing that account numbers end with either 00 or 01

Re: number of 17 digits

jonathandienst — Fri, 13 Sep 2013 12:33:42 GMT

If this is an account ID, you can load and display as text.

LOAD text(accountID) As AccountID,

...

Hope that helps

Jonathan

Re: number of 17 digits

ali_hijazi — Fri, 13 Sep 2013 12:38:04 GMT

I tried your suggestion however I'm getting 1.5688946100202e+016 as text !!

Re: number of 17 digits

jonathandienst — Fri, 13 Sep 2013 12:50:57 GMT

It looks like the ODBC driver is not handling it correctly or the database is storing it a a long numeric (so it is not subject to IEEE floating point limitations). If you have access to the load script, and understand the SQL syntax for your database, you could ensure that the database is returning a string value (use a cast/convert or equivalent function) or split the ID into two shorter numbers

SELECT

int(AccountID / 1e8) As Account1,

AccountID - int(AccountID / 1e8) * 1e8 As Account2,

...or using string slicing operations. And then use

Load

text(Account1) & text(account2) As AccountID,

...

SQL SELECT ....

Hope that helps

Jonathan

Re: number of 17 digits

pgrenier — Fri, 13 Sep 2013 12:51:37 GMT

I have had such a case with 16-digit numbers that were sometimes stored as numbers, and sometimes as text

I had to use both Num(account_nb, '################') and Evaluate(account_nb), depending on the case, in order to accomplish what was expected.

Hope this helps, cheers,

Philippe

Re: number of 17 digits

rbecher — Fri, 13 Sep 2013 13:05:42 GMT

Maybe try this, it helped in similar scenario:

Div(Field, 1e10) & left(repeat('0',10), 10 - len(text(Mod(Field, 1e10)))) & Mod(Field, 1e10) as MyNum

- Ralf

Re: number of 17 digits

ali_hijazi — Fri, 13 Sep 2013 13:50:58 GMT

I tried it on the following number 1.5688946100202e+016 and it returned 15688946100202000 I think this is correct

Re: number of 17 digits

ali_hijazi — Tue, 17 Sep 2013 12:58:13 GMT

I have the following number:15688946100202001 your function combination returns 15688946100202000 needs modification please advise

Re: number of 17 digits

hic — Tue, 17 Sep 2013 13:45:19 GMT

If you want to keep all digits, you should use Text(Field)

HIC

Re: number of 17 digits

ali_hijazi — Tue, 17 Sep 2013 14:13:08 GMT

i already tried it but it returns 1.5688946100202e+016 as text

Re: number of 17 digits

pgrenier — Tue, 17 Sep 2013 18:32:11 GMT

Hello Ali,

I unfortunately don't have access to an AS/400 database to verify this, but maybe if you converted the data to text format in your native query on the source AS/400 database, then interpret the result as Text data, you might get the expected result.

LOAD Text(NewField) as Field;

SQL SELECT LPAD(CAST(Field AS CHAR(10)), 17, '0') AS NewField FROM AS400_Table;

Regards,

Philippe

Re: number of 17 digits

Sat, 23 Aug 2014 17:07:42 GMT

Hi philippe,

Thanks a lot for your code. It really worked for me. I was really struggling for 3 days and atlast i succeeded.

Thanks,

Sase

Re: number of 17 digits

flanfranco — Mon, 02 Mar 2015 17:26:41 GMT

I'm faced same problem, i resolve it in the ETL (SQL SELECT), retrieving the INTEGER field in VARCHAR, for example:

example_table:

SQL SELECT *,

convert(varchar(), example_field_integer) AS Example_Field_string

FROM example.dbo."example_table";

STORE example_table into $(VPath)\ETL\QVDs\example_table.qvd (qvd);

DROP Table example_table;

I hope help you!

Greetings!

Flavio