Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist III
Specialist III

Hi,

Try to multiply the field by 1.

Your_Field * 1

Hope this helps

Specialist
Specialist

Hi,

Num#(Field)

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

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?

Hi,

try changing your script like:

SET ThousandSep='.';

SET DecimalSep=',';

hope this helps

regards

Marco

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

Creator III
Creator III

You can use this in the load script:

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

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

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.