topic Re: Number format code in QlikView

Number format code

s2016 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Number format code

Anil_Babu_Samineni — Fri, 09 Jun 2017 03:03:42 GMT

Are you expecting this from dummy data?

1234

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

];

Re: Number format code

s2016 — Fri, 09 Jun 2017 03:35:00 GMT

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

Re: Number format code

nagarjuna_kotha — Fri, 09 Jun 2017 03:49:18 GMT

Create 10 lines of data along with expected output.

It will helps you to get answer quickly.

Re: Number format code

Anil_Babu_Samineni — Fri, 09 Jun 2017 04:02:09 GMT

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

Re: Number format code

devarasu07 — Fri, 09 Jun 2017 04:11:08 GMT

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)

Re: Number format code

antoniotiman — Fri, 09 Jun 2017 04:30:19 GMT

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

Re: Number format code

s2016 — Fri, 09 Jun 2017 04:42:12 GMT

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?

Re: Number format code

devarasu07 — Fri, 09 Jun 2017 04:50:32 GMT

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

Re: Number format code

s2016 — Fri, 09 Jun 2017 04:57:01 GMT

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