Interpreting string as correct number, leading zeroes, and +/- sign

[richard_cioci]

I receive a text file from a data provider that contains numbers like this

Location          Number

Location A      +0000000000000005

Location B      +0000000000000016

Location C      -0000000000000002

Where the interpretation should be:

Location A           5

Location B           16

Location C          -2

However, Qlik is not correctly interpreting the string as a number. I've tried removing the leading zeroes but it removes the negative sign as well and leads to the -2 being recognized as 2.

I tried using the num# function to tell it how to interpret, but I'm not sure how to do it.

I've tried num#(Number) as Number and num#(Number,'################') as Number as well. These both lead to a blank value being entered. I've also tried just leaving it as Number, but when I do a calculation with it later, it also results in a blank value.

I should mention this number field is coming from a cross table.

Thoughts?

[sunny_talwar]

Try this:

Table:

LOAD *,

  Num#(PurgeChar(Left(Number,16), '0') & Mid(Number, 17, Len(Number)-16)) as NewNum;

LOAD * INLINE [

    Location, Number

    Location A, +0000000000000005

    Location B, +0000000000000016

    Location C, -0000000000000002

];

Seems to work for me.

HTH

Best,

S

[rwunderlich]

Try:

Evaluate(Number & '* 1') as Number

Credit to Nick in this thread.

How I can remove leading zeroes

-Rob

http://masterssummit.com

http://robwunderlich.com

[PrashantSangle]

Hi,

Try below script,

Test:

LOAD *,

Evaluate(Number) * 1 as NewNum;

LOAD * INLINE [

Location, Number

Location A, +0000000000000005

Location B, +0000000000000016

Location C, -0000000000000002

];

Regards