number of 17 digits

[ali_hijazi]

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

[Gysbert_Wassenaar]

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.

[rajeshvaswani77]

Hi Ali,

What was the first number value that returned blank?

thanks,

Rajesh Vaswani

[hic]

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

[ali_hijazi]

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

[jonathandienst]

Hi

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

LOAD text(accountID) As AccountID,

...

Hope that helps

Jonathan

[ali_hijazi]

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

[jonathandienst]

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

[pgrenier]

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

[rbecher]

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