Practical Tip - Converting Newly Appended Character Fields to Numeric Fields

Brian Element

Forums

Hello Group Members,

 

Let's say you have a logistic - freight outward data file imported in IDEA. This file contains a field RouteMasterRate with a sample value like MUMPUN-7500.

 

Now MUMPUN7500 has a logic to it. It stands for commercial freight deliveries on the Mumbai (origin) Pune (destination) freight corridor where the master rate is Rs. 7500 for a one way full load trip.

 

Now you need to map the master rate from the field RouteMasterRate to the actual rate levied to the customer to capture excess/short freight levied.

 

To meet your requirement you need the 7500 from the case above in a separate newly appended field which can then be compared with the actual rate.

 

So we set about performing Field Manipulation - Append Field using the criteria @split(RouteMasterRate, "-", "", 1, 0) to get 7500 in a separate field.

 

Now think a step ahead if you may. You need 7500 as a number and not character so that it can be compared with the actual freight rate.

 

However when you Append the new field Master Rate using @split(RouteMasterRate, "-", "", 1, 0) you will by force of habit create a Virtual Character field (since RouteMasterRate is a Character field). The Virtual Character field will not help with the comparison with the actual freight rate.

 

So in scenarios like above you can append a Character field and not Virtual Character field using the same function - @split(RouteMasterRate, "-", "", 1, 0)

 

You will get 7500 in the new field post validation in the Equation Editor. Now you can convert the very same Character field from Character to Numeric using Modify Field. This will make the master field available to you as a Number for comparison with actual freight rate.

 

However an important tip for all. While appending the Character field make sure you get the equation right, otherwise you will have to delete the field and start over again. It is unlike a Virtual Character field where you can go into the stored Equation and tweak it for correct reporting.

 

Kind Regards

 

Group Admin Team