topic Re: Text to Number not working in QlikView

Text to Number not working

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

Hi All,

Please see below extract.

As can be seen from above, the PRQTY and Calculation (QTY+PRQTY) is text, and the [Sum of QTY] and QTY fields are numeric based on the text alignment.

Trouble is, that I am trying to do a calculation QTY+PRQTY, and the result looks as follows:

Seems that when the PRQTY is equal to 0 (isnull value was changed to 0 with the hope of fixing the issue) it does not keep the QTY as the result, instead it says 0.

I believe this is a result of a formatting issue , however i have tried the below formulas:

Num(QTY) as QTY
Num#(QTY) as QTY
(Num (Trim([QTY]),'#,##0', '.' , ',')

I have also tried using the "Number" sheet and changed the format to number there, and to Fixed to etc. with no result.

I've resorted to the below formula, which yields the correct result, i am just unsure as to why the null values, cannot be converted into number.

Can Anyone share some insight?

Regards,

Re: Text to Number not working

sunny_talwar — Fri, 21 Apr 2017 12:01:41 GMT

Try with RangeSum

RangeSum(QTY, PRQTY)

Re: Text to Number not working

Anonymous — Fri, 21 Apr 2017 12:01:55 GMT

could you do a numericcount() and a missingcount() on your fields and post the example please...

Re: Text to Number not working

swuehl — Fri, 21 Apr 2017 12:09:07 GMT

Ruan, NULL is not a value, so it's not zero value.

Have a look at

NULL handling in QlikView

Math operators like + return NULL if one of the operands is NULL.

Rangesum() however, like suggested by Sunny, is converting NULL to zero in its calculations.

The option on presentation tab to replace NULL with any given text, like '0' is just for display purposes, it does not change how Qlik operates on your data.

Re: Text to Number not working

Kushal_Chawda — Fri, 21 Apr 2017 12:33:32 GMT

If Null value is used in any of the arithmetic operator (+,-,/,*) results always in Null. So in your case you need to convert NULL values to 0, so as to perform addition of two number.

Example

With Null - (2+ Null =Null )

Null converted to 0 - ( 2+0=2)

You can use Rangesum() function as suggested by Sunny

Also, you can use Alt() function alt(QTY,0) + alt(PRQTY,0)