topic Re: String to number?? in QlikView

String to number??

qlikuser222 — Tue, 11 Jul 2017 19:30:43 GMT

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

Re: String to number??

sinanozdemir — Tue, 11 Jul 2017 19:34:59 GMT

Hi,

Try to multiply the field by 1.

Your_Field * 1

Hope this helps

Re: String to number??

el_aprendiz111 — Tue, 11 Jul 2017 19:55:49 GMT

Hi,

Num#(Field)

Re: String to number??

Anonymous — Tue, 11 Jul 2017 20:48:56 GMT

Could be hidden characters that you don't see.

Try

Trim(Your_Field)*1 as YourNumberField

num#(trim(Your_Field))

num(trim(Your_Field))

Re: String to number??

Anonymous — Tue, 11 Jul 2017 20:54:24 GMT

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?

Re: String to number??

MarcoWedel — Tue, 11 Jul 2017 22:02:03 GMT

Hi,

try changing your script like:

SET ThousandSep='.';

SET DecimalSep=',';

hope this helps

regards

Marco

Re: String to number??

MarcoWedel — Tue, 11 Jul 2017 22:07:23 GMT

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

table1:
LOAD Num#(Amount,'#.##0,00',',','.') as Amount
INLINE [
    Amount
    "1.083,79"
    "1.084,85"
    "1.100,88"
];

hope this helps

regards

Marco

Re: String to number??

jcampbell474 — Wed, 12 Jul 2017 02:14:49 GMT

You can use this in the load script:

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

Re: String to number??

Anonymous — Wed, 12 Jul 2017 04:18:38 GMT

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

Re: String to number??

jcampbell474 — Wed, 12 Jul 2017 23:12:39 GMT

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.