Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
holguinmora
Contributor III
Contributor III

Convert Text to Number

Hi to all!!!

From SAP I´m getting numbers as tex in the next format and I need to convert them into a number.  On the final image an example.

On the next lines a couple of examples and the result that I´m looking for:

Original text=  1.246.861  / Result 1246861

Original text= 193.536-  / Result -193536

Original text=  6,00-  / Result -6

Any ideas?

 

Captura de Pantalla 2019-07-12 a la(s) 3.12.14 p. m..png

Labels (4)
1 Solution

Accepted Solutions
Marcos_rv
Creator II
Creator II

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', ',' , '.')   ,   num#(    TEXT ,  '#.###', ',' , '.')   

View solution in original post

7 Replies
Marcos_rv
Creator II
Creator II

try use NUM#()  FUNTION, 

ALSO 

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', '.' , ',')   ,   num#(    TEXT ,  '#.###', '.' , ',')  

holguinmora
Contributor III
Contributor III
Author

Marco hi

 

I just aply your formula and it converts a 7.500 (original format) = seven thousand five hundred into 7.5 seven point five. If it´s possible I will like to conver it into 7500.

 

Is this possible?

Vegar
MVP
MVP

image.png

Like this:

num(money#([Volumen ventas],'#.##0,00;#.##0,00-', ',', '.'),'0')

num(money#([Costo de ventas]','#.##0;#.##0-'),'0')

num(num#([Ingresos],'0', ',', '.') )

 

Marcos_rv
Creator II
Creator II

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', ',' , '.')   ,   num#(    TEXT ,  '#.###', ',' , '.')   

holguinmora
Contributor III
Contributor III
Author

Marcos Thank you very Much!!!!!

Vegar
MVP
MVP

Does this solution handle negative values @Marcos_rv ?

By the looks of it I would dine that you would need to multiply the first
part with -1 or am I missing something?

if( RIGHT(TEXT, 1 ) = '-' ,
-1 * num#( SubField(TEXT, '-' , 1) , '#.###', ',' , '.') ,
num#( TEXT , '#.###', ',' , '.')
)
Marcos_rv
Creator II
Creator II

yes, it is that the value that is entered is a string of characters (text type), but for the sum you need to pass it to number, also the final character is the one that decides if it is negative or not. it is true the correction you mention, you need to multiply by -1 in the case that it is negative. thank you for the correction. Regards!!!