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.

[salmankojar]

Speed gets slow only when i use Evaluate function, other wise it works fast..maybe evaluate function takes time..

[salmankojar]

Tried this already..it doesnt work..

[BrunPierre]

Let's troubleshoot, post the load script.

[Or]

Should work fine. Use Marcus's suggestion to get the number part and keep the existing aspect in the string part.

[salmankojar]

See when i enter the below script

[sales]:
LOAD
"Material Number"

FROM [lib://Intermidiate_Qvd (gyandairy_qlikadmin)/Sales_Intermidiate1.qvd]
(qvd);

It loads quickly.

But When i enter evaluate function it becomes too slow..see the script and loading result below

[sales]:
LOAD
Evaluate("Material Number")

FROM [lib://Intermidiate_Qvd (gyandairy_qlikadmin)/Sales_Intermidiate1.qvd]
(qvd);

[marcus_sommer]

It's not surprising. The first load is optimized and the second one enforced a processing of the data. Like above mentioned the values have to much digits - at least if all leading zeros are needed.

Beside of it - if any transformation is needed this step should be done already by the creation of the qvd. 

[salmankojar]

the above field is in two SAP tables..one field is stored in string format as shown in above image but the other field values are stored in numeric format containing only last digits like 10001,10002,200085,...

so to join table based on this field i have two options.

1. make string field into numeric format which will remove all the leading zeros to match the field in other table which i am trying to do.
2. other options is to put extra leading zeros in the numeric field to match the field in other table which is in string format which i dont think is possible (in my understanding).

[marcus_sommer · Accepted solution]

Both transformations are possible. If the lengths of the strings and numbers are static it would be simpler but also by varying lengths it's not difficult, for example with approaches like:

num(right('000000000002800138', len('000000000002800138') - findoneof('000000000002800138', '123456789') + 1))

repeat(0, 18 - len(2800138)) & 2800138

In regard to a performance point of view the numbers will cause a lower footprint as the strings.

[BrunPierre · Accepted solution]

Option 2 from my understanding.

LOAD *
Repeat('0', 18-Len(Field)) & Field as NewField
Inline
[
Field
10001
10002
200085
];

[menta]

try to use 

Num( trim(Yourfield))

or NUM#(trim(Yourfield))

or rangesum(yourfield)