Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
s2016
Contributor III
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
devarasu07
Master II
Master II

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

];

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
s2016
Contributor III
Contributor III
Author

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

nagarjuna_kotha
Partner - Specialist II
Partner - Specialist II

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
devarasu07
Master II
Master II

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

antoniotiman
Master III
Master III

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
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

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