Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikmeplease
Contributor III

Text to Number not working

Hi All,

Please see below extract.

Capture.JPG

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:

Capture2.JPG

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.

Capture3.JPG

Can Anyone share some insight?

Regards,

4 Replies

Re: Text to Number not working

Try with RangeSum

RangeSum(QTY, PRQTY)

robin_hausdoerfer
Valued Contributor III

Re: Text to Number not working

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

MVP
MVP

Re: Text to Number not working

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

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)