TI: When StringToNumber returns an undefined value...

[harrytm1]

Hi all,

I have a source file from SAP where the Ship To Number is a mixture of numeric charactes preceded with series of "0" (e.g. 000000897) and alphanumeric characters (e.g. AH1234). Note that the string length varies as well.

I want to truncate those with padded zeroes e.g. from 00000897 to 897 and use them as element codes. I decided to use StringToNumber to remove the zeroes, then convert the result to string again with NumberToString.

However, this obviously results in error when StringToNumber applies on alphanumeric codes e.g. AH1234.

How do I write the script such that it can check that, if StringToNumber returns an error, the calculated variable shall simply be the Ship To Number? If no error, then use the truncated numeric string.

Thanks!

harry

[Duncan P]

Try

Code: Select all

while ( 1 = SCAN( '0', string ) );
    string = DELET( string, 1, 1 );
end;

[Alan Kirk]

Hi all,

I have a source file from SAP where the Ship To Number is a mixture of numeric charactes preceded with series of "0" (e.g. 000000897) and alphanumeric characters (e.g. AH1234). Note that the string length varies as well.

I want to truncate those with padded zeroes e.g. from 00000897 to 897 and use them as element codes. I decided to use StringToNumber to remove the zeroes, then convert the result to string again with NumberToString.

However, this obviously results in error when StringToNumber applies on alphanumeric codes e.g. AH1234.

How do I write the script such that it can check that, if StringToNumber returns an error, the calculated variable shall simply be the Ship To Number? If no error, then use the truncated numeric string.

If the file isn't too long and can therefore handle the iterations involved, then Duncan's method will work well for you.

Otherwise it's worth bearing in mind that while the StringToNumber function will throw an error when there are alpha characters, the rules Numbr() function doesn't. It simply converts whatever numeric characters it finds, and if it doesn't find any returns 0. It would then be possible to do an If() text so that If the converted value is 0, use the original Ship To number, otherwise use the sting conversion of the Numbr() function's result.

Either way should work for you.

[harrytm1]

thanks Alan and Duncan!

Thanks for pointing out Numbr() returning a 0 if it is alpha num. Will build the IF condition check using it.

[Duncan P]

Unfortunately while in a rule NUMBR just returns 0 for a non-numeric argument it appears that in a process it also puts a minor error in the error log which takes the time up a bit.

On my machine for stripping a 10 character code of which the first 5 characters are "0" I get the following results for a million records

Code: Select all

NumberToString(StringToNumber())          2.3 s
Looping SCAN & DELET                      4.1 s
NumberToString(NUMBR()) numeric code      2.3 s
NumberToString(NUMBR()) alphabetic code   9.4 s

Setting MinorErrorLogMax does not appear to affect the time taken using NUMBR on alphabetic codes and the process is still reported as having finished with minor errors.

So I suppose which method you use depends on the expected ratio of alphabetic to numeric codes, and on whether you want the process to report a clean finish or not.

[Alan Kirk]

Unfortunately while in a rule NUMBR just returns 0 for a non-numeric argument it appears that in a process it also puts a minor error in the error log which takes the time up a bit.

Interestingly (though I probably shouldn't be surprised ) the behaviour is inconsistent between tabs. Say the code that you wanted to convert was the text 'qwerty'. If this appears in your data source, then you're correct, you get an error log. Try exactly the same conversion using a hard coded string in the Prolog tab... and it will not generate an error. For a programming language which is not over-endowed (or, indeed, endowed at all) with error handling capability, it would be nice if they could at least keep the frapping definition of what an error is consistent.

That having been said I think that there may be another reason why Harry may be better off using your method. It's not completely clear from his question but if he wants to use (in his example) "AH1234" as is when it contains an alpha component, then using the Numbr() method conversion this is going to come up as the value 1234. It would therefore be indistinguishable from a code like "AZ1234" or even 0000001234.

[harrytm1]

Try

Code: Select all

while ( 1 = SCAN( '0', string ) );
    string = DELET( string, 1, 1 );
end;

Hi Duncan,

I don't quite understand your above suggestion. Is "1" a variable?

To Alan,
Yes, I'm afraid, in the case of alphanumeric, I will need to retain the original form i.e. "ABC123" and not trim it to "123".

thanks!

[Alan Kirk]

Try

Code: Select all

while ( 1 = SCAN( '0', string ) );
    string = DELET( string, 1, 1 );
end;

Hi Duncan,

I don't quite understand your above suggestion. Is "1" a variable?

It's a constant. Duncan's code is the reverse way around from the usual way of expressing it, and I'll admit that I had to do a double take as well, but it still works fine.

The test that is used in the While could also be expressed as

Code: Select all

While (SCAN( '0', string ) = 1);

That is, do it while the Scan function finds a '0' character in position 1 of the string. If it finds that character, delete it. Keep going until the Scan() function no longer returns 1.

[Duncan P]

I used to be a C programmer where = is assignment and == is equality. A common mistake in C is to write

Code: Select all

if ( variable = constant )

which assigns <constant> into variable, instead of

Code: Select all

if ( variable == constant )

which does the comparison.

One way to avoid doing this is always to put the constant on the left of a comparison

Code: Select all

if ( constant == variable )

which means that if you get it wrong

Code: Select all

if ( constant = variable )

you get a compile error instead of a hard to track bug.

Even though in TI = is used for comparison I just can't kick the habit.

It is similar to the reason I always put loop counter increments as the first line of the loop.

Code: Select all

counter = 0;
while ( counter < limit );
    counter = counter + 1;
    ...
    ...
end;

That way you can see straight away if the loop is well formed, and if you do it often enough (and I have) it gets burned into your fingers and you don't forget.

[Anthony]

Hi,

The minor error using the numbr function is due to the presence of the "e" character in some of the values. This character is used by TM1 in the number definition (2e3 = 8). I used the code below to get around this issue (i was trying to identify if my value was a string or a number) :

if(SCAN( 'EE', V1) =0);
if(NUMBR('0'|V1|'0') >0);
...
Endif;
Endif;

Hope this will help !

Regards,

Anthony