Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salmankojar
Creator
Creator

Converting Number Stored in a text format to a number format

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.

Screenshot 2023-05-08 160122.png

Labels (4)
21 Replies
salmankojar
Creator
Creator
Author

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

salmankojar
Creator
Creator
Author

Tried this already..it doesnt work..

 

BrunPierre
Master
Master

Let's troubleshoot, post the load script.

Or
MVP
MVP

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

salmankojar
Creator
Creator
Author

See when i enter the below script

[sales]:
LOAD
"Material Number"

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

It loads quickly.

Screenshot 2023-05-09 175224.png

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);

Screenshot 2023-05-09 175827.png

 

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
Creator
Creator
Author

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

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
Master
Master

Option 2 from my understanding.

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

BrunPierre_1-1683639839859.png

menta
Partner - Creator II
Partner - Creator II

try to use 

 

Num( trim(Yourfield))

or NUM#(trim(Yourfield))

or rangesum(yourfield)