Number format code

[s2016]

I have numbers like

1234.123456789

1.123456

123456789.123456789

What format code should I use so that no digits are lost. Also, there are nulls in some rows and I want to replace them with 0.

Please help me understand how the format codes work. I tried using '##0.#' but it trimmed the decimals to 1 decimal place.

[Anil_Babu_Samineni]

Are you expecting this from dummy data?

1234

1

123456789

For this, I used like below

LOAD Dummy, SubField(Dummy,'.',1) as Output1, Round(Dummy) as Output2 Inline [

Dummy

1234.123456789

1.123456

123456789.123456789

];

[s2016]

Hi Anil, I want the numbers to remain the same. I just want to replace null values with 0.  

[nagarjuna_kotha]

Create 10 lines of data along with expected output.

It will helps you to get answer quickly.

[Anil_Babu_Samineni]

Sorry, This time not clear to me, Would you explain the result set as you required

[devarasu07 · Accepted solution]

Hi,

Are you trying to do in text object? if so you can try like below,

=alt(Num#(123456789.123456789,'#,##0.00;(#,##0.00)'),0)

[antoniotiman]

Hi,

You don't need Num() function and any format code.

However QlikView accepts mas 14 Digits (12345678,12345678 -> 12345678,123456)

If You want chenge Null to 0, in script :

NullAsValue YourField;

Set NullValue = '0';

Load .....

NullAsNull YourField;

Regards,

Antonio

[s2016]

Thanks everyone. I think to get my desired solution I can use the ALT function.

But I am trying to understand how # and 0 work in number format. Do I need to put as many # as number of possible decimal places in any of the numbers to get the numbers as is without losing any digit?

eg) num(123456.123456789, '######.#########')

Is this correct?

[devarasu07]

Hi,

Not required, simply u can use like below in text object.

=alt(Num#(123456789.123456789),0)

Note:

using alt can replace null as 0

Num# will return actual number

Hope this helps you

Regards,

Deva

[s2016]

Thanks Devarasu. I got the desired results. But I am trying to understand how format code # works. Any information on this would be appreciated.