Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
shashank_sood
New Contributor III

Number format code

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.

1 Solution

Accepted Solutions
Highlighted
devarasu07
Honored Contributor II

Re: Number format code

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)

Capture.JPG

View solution in original post

9 Replies
Highlighted

Re: Number format code

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

];

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
shashank_sood
New Contributor III

Re: Number format code

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

Highlighted
Partner
Partner

Re: Number format code

Create 10 lines of data along with expected output.

It will helps you to get answer quickly.

Highlighted

Re: Number format code

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
devarasu07
Honored Contributor II

Re: Number format code

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)

Capture.JPG

View solution in original post

Highlighted
antoniotiman
Honored Contributor III

Re: Number format code

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

Highlighted
shashank_sood
New Contributor III

Re: Number format code

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?

Highlighted
devarasu07
Honored Contributor II

Re: Number format code

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

Highlighted
shashank_sood
New Contributor III

Re: Number format code

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