
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Option 2 from my understanding.
LOAD *
Repeat('0', 18-Len(Field)) & Field as NewField
Inline
[
Field
10001
10002
200085
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @salmankojar not so much of a logical solution, but here's one.
LOAD
Evaluate([Item Number]) as [Item Number]
...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this wont work coz i have to join this field with a similar field in another table.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you please explain this in detail..i didnt understood your solution of
Num(ItemCodeNumeric) AS ItemCodeNumber and also mapping load solution..?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or use Dual() to have both the numeric and string aspect.

- « Previous Replies
- Next Replies »