I need to identify the values in a field which are of 9 series and greater than or equal to 99999 and convert them to 0s in DataStage i.e atleast first 5bytes of the field are 9s. Here are some examples to explain the situation better 999.00 - Don't convert 9999.00- Don't convert 99999.00- Convert to 0 999991.00-Convert to 0 100000.00- Don't convert 999999.00- Convert to 0 123456.00-Don't convert 9999999.00-Convert to 0 1999999.00-Don't convert 1000000.00-Don't convert
Right now i have If Convert('9','', Trim(Trim(Field(NullToZero(AMT),'.',1)),'0','L')) = '' Then 0. This converts any 9s but our goal is to convert 9s of 99999 and greater only. Please help with solution
Use Field() function to work with just the digits to the left of the decimal placeholder. Test that this value has at least five characters, and that they are all "9".
If Len(Field(AMT,".",1,1) >= 5 And Convert("9","",Field(AMT,".",1,1)) = "" Then 0 Else AMT
Even better would be to resolve the Field() function into a stage variable, so that it's not executed twice, and use that stage variable in the expression.