Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
devans_1
Creator
Creator

Num () question

I have some data that has been created using SUM (). Some of it is integer, some of it is not. I would like integers displayed without decimal places. But I would like the data that contains decimal places to stop at 2 dp. e.g. Data of

1

2

3.45678

would display as

1

2

3.46

If I just display the data, I get

1

2

3.45678

If I use the NUM function (ie NUM (<field>, '#,##'), I get

1.00

2.00

3.46

Does anyone know what I am doing wrong with NUM ? My understanding was that the use of the hash meant the decimal places were flexible and would not appear if they weren't there but they are appearing.

13 Replies
javier_florian
Creator III
Creator III

Hi David,

Your function is fine, format is applied to all data.

-JFlorian

Not applicable

Hi,

You can round numbers with 2 decimals in your object :  Round(NumericField,0.01) then display numbers naturally.

ashfaq_haseeb
Champion III
Champion III

Hi

Try below

num(Num,'#######,###.##')

Regards

ASHFAQ

Anonymous
Not applicable

num function will format all the numeric values in that field/column to the format you specify.

Not applicable

Did you set any Number format under Number tab of Object properties?

rajat2392
Partner - Creator III
Partner - Creator III

Hi David,

Try this expression

if(Subfield(<field>,'.',2)>0,Num(<field>,'#.00'),<field>)

Regards

Rajat

devans_1
Creator
Creator
Author

Thanks for your replies. I realise that the format is applied to all values in the column - it's just that I am trying to find the correct format that will remove trailing decimal places.

num (NUM, '#######,###.##')

unfortunately produces the same results as my initial problem. I have considered ROUND and may have to go down this route. It's not perfect as ROUND also adds decimal places when they are not required but if used earlier in the script and then later summed, unecessary trailing dps are removed.

devans_1
Creator
Creator
Author

I have tried changing the number format but this just places trailing zeroes. Rajat's answer would work but i smessy because I have to apply it to 12 different complicated expressions. I think ROUND is the best answer to go with.

Thanks for all your help.

its_anandrjs

Try to load your table like below script if you want a digit number 1.345 to be 1.34 or 2.145 to be 2.14 or 3.45678 to be 3.45 then try to load like below table

LOAD *,Num(SubField(Number,'.',1)&'.'&Left( SubField(Number,'.',-1),2)) as NewNumber;

LOAD * Inline

[

Number

1.345

2.145

3.45678

];

OutPut will be

==========

1.34

2.14

3.45