Format text as number in data load

[maasool]

For some reason, in my data load, long numbers (15 characters) are interpreted as text. I used following formula to convert all values from this field to numbers:

NUM# (FIELD, '#.#' , '' , '.' ) as FIELD

It is not giving the result I want, as long numbers are still formatted as text. I see it from formatting in pivot table (long numbers are aligned to the left as text, while shorter numbers are aligned to the right) and also when I am exporting the data to excel (long numbers are formatted as text).

Reason why I want to format them as numbers, is because I need to link values from another table based on these numbers.

[Stoyan_Terziev]

Hi Maasool,

Can you try simply this:

NUM(FIELD) as FIELD

 Keep in mind the Num# parameters are Text [,format[,dec_sep [,thou_sep]]]

So: Num#(Field, Format, DecimalSeparator, ThousandsSeparator) as FIELD

Probably try this:

NUM# (FIELD, '#.###' , '.' , '' ) as FIELD

I hope that helps!

Kind regards,

S.T.

[maasool]

Hi, thanks for thinking along!

When I used this, then long numbers disappeared completely during data load. They don't exist in app pivot.

NUM(FIELD) as FIELD

This gave the same result as my formula:

NUM# (FIELD, '#.###' , '.' , '' ) as FIELD

[maasool]

I found, that some values in this field actually contain text (one word). Does it change anything?

[maasool]

any ideas how to solve it?

[Stoyan_Terziev]

Hi Maasool,

I presume you'd like to remove any text characters from your strings but keep the numbers. Let's try this:

NUM#(KeepChar(Text([FIELD]), '1234567890.'), '#.###' , '.' , '' ) as FIELD

Let me know if that does the trick for you.

If not, can you post a couple of examples for how the values in your column look like?

Kind regards,

S.T.

[claudialet]

Try the dual () function.

  NUM ( DUAL (Text( [FIELD]  ) , '# ##0.0000' ) as FIELD

It formats both text characters and numbers . 

[maasool]

Thanks Stoyan!

Tried it, but still long numbers are treated as text. Please see attached example. Long numbers are aligned left and QS cannot find matching value from another table to adjacent column. For smaller numbers, it is not a problem.

[Stoyan_Terziev]

Hi Maasool,

One of the problems you may be facing is that if you convert the numbers to 'numbers', they all need to have consistent format. Aka: you can't/shouldn't have one number with 3 digits after the decimal and another with 5. If that's what you are trying to achieve, you may want to rethink if the 'Text' format isn't the suitable one.

Let's give it one more try. This will unify the number format to 4 digits after the decimal.

NUM(KeepChar(Text([FIELD]), '1234567890.'), '# ##0.0000') as FIELD

Regards,

S.T.

[maasool]

Hi Stoyan,

It didn't help. My figures are identification numbers, they don't have any numbers after comma.