Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
];
Hi Anil, I want the numbers to remain the same. I just want to replace null values with 0.
Create 10 lines of data along with expected output.
It will helps you to get answer quickly.
Sorry, This time not clear to me, Would you explain the result set as you required
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)
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
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?
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
Thanks Devarasu. I got the desired results. But I am trying to understand how format code # works. Any information on this would be appreciated.