Create a new field

Lois

Forums

Hello,
Please I want to create a new field containing just the digits after the word "MT". Notice that some of the numbers are displayed in a range. How can I use IDEA to fill in the gap by specificaly identifying all the numbers within the ranges. It would probably require creating a new field for each number in that range. Your help is most appreciated.
PRODUCTION OF 467890  ABC TO180000 XYZ MT:867897-7900
PRODUCTION OF 1289450 BAFS ABC TO 40000 BAFS XYZ MT 886001
PRODUCTION OF 3398732 ABC TO  45000 XYZ MT 889926
PRODUCTION OF 660999 ABC TO 66000 XYZ MT 885602-885603
Regards,
Lois

Steven Luciani Tue, 05/29/2018 - 07:52

Hi Lois,

Based on your example and assuming the MT is always at the end of the string followed by numbers or numbers separated by dashed the equation below will work.

@Justnumbers(@mid(col1,@isini("MT",col1),20))


The length of 20 can be increased if you have numbers or numbers separated by dashes larger then 20 digits/characters long.


Again assuming that the MT is located at the end of the string followed only by numbers and special characters like colons or dashes this equation will also work.

 

 

@JustNumbersTrailing(@strip(col1))

 

If you have any other itereations of the string please post and we can expand the equation to capture all instances in one step so only one new field needs to be created.


Cheers,

Steve

 

 

 

 

 

Brian Element Tue, 05/29/2018 - 08:03

Hi Lois,

Steve showed you how to extract the numbers.  If you want to create individual fields for items like 867897-7900 where you want one field to hold 867897, next field 867898 until 867900 then you would have to script this part as you can't create new fields from with the equation editor.

Thanks

Brian

Lois Wed, 05/30/2018 - 12:07

Thanks Steve and Brian. Yes, I did want to create a new field(s) using the criteria.
I did get a workaround for creating and filling in the missing field. I will send across the formula tomorrow so you can have a look.
Many thanks,
Lois

Lois Thu, 05/31/2018 - 09:13

Hello All,
As promised, please find attached my work-in-progress. As you can see, some records are still not working well. Feedback would be most appreciated.
Many thanks,
Lois

Brian Element Fri, 06/01/2018 - 07:42

Hi Lois,

Thanks for the script and file.  So just to make sure that I understand you, is this the output you are hoping to create?

If so, is the maximum number of fields that you want created always 3 or is it something that is unknown and based on the range?  If there is only one item or less then the maximum do you expect a blank, 0 or something else in the field?

Brian

 

Lois Mon, 06/04/2018 - 06:48

Hi Brian,
Yes, I will like to have that as an output to be applied on similar files going forward. No the maximum number is not always 3. It is determined by the range. When there is only one item, I will expect a 0 since it is a numeric field.
Lois