Converting Number Stored in a text format to a number format

[salmankojar]

i am working with a project which have a field containing item code such as '000000000000010000' in this text format and i am trying to convert this into number format using this statement 

NUM(NUM#("item code"))

But the above statement gives null values.it doesnt convert the field value.

i tried TRIM function also but it still remains the same.

i have attached an image of it for reference.

Please help me..

Thankyou.

[BrunPierre]

Hi, @salmankojar  not so much of a logical solution, but here's one.

LOAD
Evaluate([Item Number]) as [Item Number]
...

[Chanty4u]

Try this 

Num(ItemCodeNumeric) AS ItemCodeNumber

Or 

Num#("item code", '#############') AS ItemCodeNumber

Or 

MappingTable:

MAPPING LOAD

    "item code",

    "numeric value"

FROM [path_to_mapping_tabl]

;

ApplyMap('MappingTable', "item code") AS ItemCodeNumber

[marcus_sommer]

The values have too much digits to be handled as numbers. If all leading zeros are important you need to keep the information as string. If not you may just cut the last 7 digits, maybe like:

num(num#(right(Field, 7)))

[salmankojar]

@BrunPierre thanks for the above solution.it does works but data fetching speed becomes too slow..is there any other way to solve this problem???

thankyou..

[salmankojar]

this wont work coz i have to join this field with a similar field in another table.

[salmankojar]

can you please explain this in detail..i didnt understood your solution of

Num(ItemCodeNumeric) AS ItemCodeNumber and also mapping load solution..?

[marcus_sommer]

For the join you could keep this field as string or you applies the same transformation on both sides respectively the appropriate ones if the fields have a different format.

[BrunPierre]

The speed is dependent on server traffic, driver speeds and the load in the network, when loading from a database. You need to analyze these as to why the speed is too slow. From the driver configuration, it is possible to increase your connection speed. In addition, the data model should only be populated with fields which are required for your analysis. LOAD * and SELECT should not be used.

[Or]

Or use Dual() to have both the numeric and string aspect.