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
aveenm293
Offline
Last seen: 4 years 10 months ago
Joined: 10/23/2012 - 16:02
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.
Hi Brian
I will look at and revert back.
Thank you
Regards
Aveen
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
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.
Hi Brian
Thank you very much. I will give this a go.
Regards
Aveen
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.
Hi Brian
I actually do need some help. Would you be able to write the IDEAScript for me please?
Thank you
Aveen, can you try this script out.
Sure, it should not take too long.