Need to strip a value out of a field based on its size

[mseedig]

I've been banging my head against any number of walls trying to accomplish what I imagine to be a very simple task. One field in particular that we get inbound from our trading partners can be from 1 to 10 digits. However, anything less than 7 is a problem for our database, so what we want to do is, if the size of the value is less than 7 digits, then we want to pass our database a blank value for that field. I have been toying with various permutations of an = IF (SIZE (field) < 7 type rule, but can't find an arrangement that will build successfully and work (I've been able to make it blank all the time, even when the value is 10 digits, but we want to pass that value to our application); and I haven't (yet) found anything in a thread here that points me in a direction on this (but I'm not giving up!). If anyone has any suggestions, I'd welcome them. I'm about at my wit's end on this. Thank you in advance!

 

[eyetry]

YOu could set/confirm the column value in the tree to pad to 7 bytes defining the pad that meets your needs. left/right -space/zeros etc...

 

[janhes]

=IF (SIZE (field) < 7,none,field)

 

[mseedig]

We're not wanting to pad the value if we can keep from doing that. We just want to empty the field the maps pass to the database if the value is smaller than we expect.

I've tried the =IF (SIZE (field) <7, none,field) in every combination I can think of, and I get either a lengthy list of build errors, or it strips the value out regardless of the size. I'm guessing this is so simple that I just can't seem to get it right. Ascential support is looking at it as well, but I've not heard a response from them yet.

 

[mseedig]

Oh, one other thing...We've tried this is two different maps. First we tried it on the map that translates the inbound data into XML. Then we tried it on the map that performs edits on the XML. Thus far, neither has had any success.

 

[janhes]

What version of DSTX are you using? I have tried the rule in a map on 6.7.1 on Windows XP wnd it compiles ok. Perhaps you've got a bug.

 

[jmanj]

Perhaps you need to convert the field into numeric then compare. i.e., IF(SIZE(TEXTTONUMBER(field) < 7


or you can also test the first 4 characters
if they are greater than zero.

Just a thought.

 

[mseedig]

Thanks to all. The solution that finally worked is this:

=IF ( SIZE ( LEAVEALPHANUM (0051_Employee_Phone Field:Input) ) > 6, 0051_Employee_Phone Field:Input, NONE )

Unknown to me at the time, this field is a fixed length field, so the spaces were being counted when there were fewer than 7 numbers in the field. Now on to the next big thing.