Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

String to number??

Hi all,

I am trying to convert string to number.

Below values are coming in as string and I want to convert into number. When I use num() in backend it gives no values.

Can anyone help me?

Thanks

9 Replies
Highlighted
Specialist III
Specialist III

Hi,

Try to multiply the field by 1.

Your_Field * 1

Hope this helps

Highlighted
Specialist
Specialist

Hi,

Num#(Field)

Highlighted
Specialist II
Specialist II

Could be hidden characters that you don't see.

Try

Trim(Your_Field)*1 as YourNumberField

or

num#(trim(Your_Field))

or

num(trim(Your_Field))

Highlighted
Specialist II
Specialist II

Actually I just took a closer look at your numbers.

Is the decimal point supposed to be a comma? 

Or is the comma supposed to not be there?

Highlighted

Hi,

try changing your script like:

SET ThousandSep='.';

SET DecimalSep=',';

hope this helps

regards

Marco

Highlighted

if you want to keep your default separator settings though, you might as well load the Amount field like:

QlikCommunity_Thread_267262_Pic1.JPG

table1:

LOAD Num#(Amount,'#.##0,00',',','.') as Amount

INLINE [

    Amount

    "1.083,79"

    "1.084,85"

    "1.100,88"

];

hope this helps

regards

Marco

Highlighted
Creator III
Creator III

You can use this in the load script:

Num#(PurgeChar(Amount,','),'#.#####') as Amount

Highlighted
Creator
Creator

Num() is for formatting a number  as text (changing the text representation) and Num#() is interpreting a text as number (changing the numeric representation).

Highlighted
Creator III
Creator III

Yes, that is correct.  QV is usually excellent at (automatically) determining the correct format to use.  In this case, using Num# will ensure that it's interpreted correctly (as a number) - just in case the decimal point and proceeding comma cause an issue.  This should ensure that the field can be used in expressions.