Custom function to determine the value at the seventh position

aveenm293

Forums

Hi Brian
I am trying to write a custom fucntion that will return the value at the position number seven from a 13 digit number eg:
7801315046080 - The value at position seven is "5" in this case.
Once I have this value, I need to write an IF statement as follows:
If the value at position 7 is between 0 and 4, it must return "Female" and "No Exception" in two different columns and If the value is at position 7 is between 5 and 9, it must return "Male" and "Exception" in two different columns.
I have tried writing the function using @Mid and @GetAt to get the value with no success. I am using IDEA v8.5.
Any ideas?
Thank you
Regards
Aveen
 
 
 
 

Brian Element Tue, 04/08/2014 - 13:12

Hi Aveen,

You can try something like this: @if(@val(@mid(@lTrim(@Str(TEST, 15, 0)),7,1)) < 5, "Female", "Male").

I made the assumption that the field was numeric.  So you first have to turn it into a character field using the @str, I used 15 for the lenth just to make sure it is long enough and then the @ltrim to get rid of any leading spaces that might be added.  I then use the @mid to extract the number in position 7 and turn it back into a number for the if statement.  You will have to do two virtual fields and enter the equation twice to also capture the "No Exception" / "Exception" info.

aveenm293 Wed, 04/09/2014 - 02:18

Hi Brian
I will look at and revert back.
Thank you
Regards
Aveen
 
 

aveenm293 Thu, 04/10/2014 - 05:56

Hi Brian
I have given this a go. The number will always be 13 digits long and of character time. I have updated the script below but an struggling to get into a custom function:
@if(@val(@mid(TEST, 7,1)) < 5, "Female", "Male")
@If(TEST="Female", "No Exception", "Exception")
My function needs to create 2 new fields - One to show the Gender and one to show the No Exception / Excception.
Thank you
Regards
Aveen
 

Brian Element Thu, 04/10/2014 - 06:57

Hi Aveen, you can't do this with a custom function.  A custom function will only work on one field at a time, so you would have to run the function twice.  In order to get both fields created you will have to create an IDEAScript to do what you want in one pass.

aveenm293 Fri, 04/11/2014 - 07:18

Hi Brian
Thank you very much. I will give this a go.
Regards
Aveen

Brian Element Fri, 04/11/2014 - 07:25

Let me know if you need some help.  In the script you just need to capture the TEST field and then create two vitual fields using your equation.