Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
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
sinanozdemir
Specialist III
Specialist III

Hi,

Try to multiply the field by 1.

Your_Field * 1

Hope this helps

el_aprendiz111
Specialist
Specialist

Hi,

Num#(Field)

atkinsow
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))

atkinsow
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?

MarcoWedel

Hi,

try changing your script like:

SET ThousandSep='.';

SET DecimalSep=',';

hope this helps

regards

Marco

MarcoWedel

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

jcampbell474
Creator III
Creator III

You can use this in the load script:

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

krishnamraju513
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).

jcampbell474
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.