SUBST and SCAN with TM1 10.1.1

[JimBW]

We are upgrading to TM1 10.1.1 and have encountered some issues using various functions with TM1 10.1.1. We have several load process on our existing TM1 production server (TM1 9.1) that have been running for years without any issues. But when we copy the production database to our TM1 10.1.1 test server, these same processes that utilize the SUBSTR and SCAN functions are failing.

For example, the following function is passed DGLJ, which is a string variable containing a JDE Julian date (i.e.: 113043.0000):

MMM_DD=CellGetS('Julian_Conversions',subst(DGLJ,1,scan('.',DGLJ)-1),'mmm d');

The SUBST/SCAN removes the decimal point and trailing zeros, and the final value is used by the CellGetS function to retrieve the equivalent JDE date, formatted as MMM D. (In this example, 113043 returns ‘Feb 12’ as the date.)

While testing this process on the TM1 10.1.1 server, the process fails for every row of data with the following error:

Data Source line (x) Error: MetaData procedure line (5): Invalid key: Dimension Name: "Julian_Dates", Element Name (Key): ""

And this is MetaData line 5:

MMM_DD = CellGetS('Julian_Conversions', subst(DGLJ,1, (scan('.', DGLJ) - 1)), 'mmm d');

If we modify the function and replace the SUBST/SCAN with a hard coded value of 113043, the process runs and no longer causes an error. However, other functions in the process also error out:

Data Source line (1) Error: MetaData procedure line (25): Component Element " " not found. MetaData line 25 consists of the following statement:

And MetaData line 25 contains the following:

DIMENSIONELEMENTCOMPONENTADD('jslscust',Billto,Billto_Shipto,1.000000);

The ‘Billto’ variable contains the results of the following function:

Billto='Bill-To '|subst(AN8,1,scan('.',AN8)-1);

Again, this process has worked flawlessly for several years and only after attempting to run it under TM1 10.1.1 are we starting to see issues with functions that reference the SUBST / SCAN functions. Just wondering if anyone else has experienced this issue?

Thanks!

[lotsaram]

If you're going all the way from 9.1 to 10.1 then you are crossing the Unicode divide and my guess would be that this is where your issue is. If you run the code function on the 7th character of the JDE date string versus code on the hard coded dot character in the code are you actually searching for the correct character?

[kempzhong]

It looks like a character encoding issue. Have you tired to print out the source and result of the SUBST and SCAN function into a text file and check what happen?

[JimBW]

I was wondering if this issue might be unicode related.

What's interesting is that when the formula is open in the editor and you click the evaluate button, the function returns the correct results for all the formulas that use SUBST / SCAN. We only see the error when the process is actually executed.

[tomok]

What's interesting is that when the formula is open in the editor and you click the evaluate button, the function returns the correct results for all the formulas that use SUBST / SCAN. We only see the error when the process is actually executed.

That's because when you click on the evaluate button it is only evaluating the first record in the data source. What about all the other records? There is more than one isn't there? The evaluate button is only there to help you write your code, it doesn't guarantee it will work correctly for all the records in the data source.

[JimBW]

When I output the source data and the function results to a text file, this is what I'm seeing on the TM1 9.1 server:

"107240.000000","107241.000000","113043.000000","Bill-To 107240","107240 107241","107818 107817"
"107420.000000","107421.000000","113045.000000","Bill-To 107420","107420 107421","Feb 14"
"107818.000000","107817.000000","113002.000000","Bill-To 107818","107818 107817","Jan 2"

The first three values of each row are the source data, the last three are the results of the function. When I output the source data and the function results from our TM1 10.1.1 server, this is the output we're seeing:

"107240.000000","107241.000000","113043.000000","Bill-To "," "," "
"107420.000000","107421.000000","113045.000000","Bill-To "," "," "
"107818.000000","107817.000000","113002.000000","Bill-To "," "," "

(Note: Only the first three lines of data are shown, there are over 7,000 rows and each one is encountering the above mentioned error.)

I did find that if I modify the source SQL statement to make the necessary data conversions before pulling the data into TM1 - eliminating the need for the SUBT / SCAN functions - the process loads successfully.

[lotsaram]

Yes but if you ASCIIOutput the following ...
NumberToString(Code(SubSt(AN8, 7,1))) or NumberToString(Code(SubSt(DGLJ, 7,1)))
versus
NumberToString(Code('.'))

Then what do you get? My guess is still that the "dot" character is coming through with a different ASCII character encoding and therefore Scan is returning 0 and so you get no string. I am pretty darn sure that nothing has changed in the way that either SubSt or Scan functions work so the answer has to be in the variables.

Also since the JDE date fields have a consistent format why search for the dot in the first place, why not just substring the first 6 characters to get the index value you need? Seems to be no need to use Scan at all as the number of characters needed to represent the date index is not going to change (well it will change but not in our lifetime).

[JimBW]

I'm not so concerned about the JDE date, since it is a fixed length, have only included it in this thread as an example. There are three other variables used in these processes that are not fixed length, which required the SUBST / SCAN functions.

Ran two quick tests this morning using the following functions:
V7=NumberToString(Code(SubSt(AN8, 7,1),1));
V8=NumberToString(Code(SubSt(DGLJ, 7,1),1));
V9=numbertostring(code('.',1));

The first test was without the 'Use Unicode" box selected on the Data Source tab, and this is a sample of the output:
ASCIIOutput('c:\TestCube.cma', V7, V8, V9 );
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"

The second test was with the "Use Unicode" box selected, similar results.
Unicode box selected on Data Source tab.
ASCIIOutput('c:\TestCube2.cma', V7, V8, V9 );
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"
"0","0","46"

When the function is evaluated on the Variables tab, a value of 46 is returned for all three functions. But when the process actually executes, 0 is returned.

[lotsaram]

Well I think you have your answer then! It looks like when the query is executed there is no 7th character in the date index fields and these are being treated as a whole number with no decimal precision. The issue not that the Scan or SubSt function is working differently between versions but that the variables are being evaluated differently to start with.

I notice that you have an8 and dglj declared as string variables. I would be willing to bet big bucks that these are numeric fields in the source SQL database. Usually I don't care one way or the other but I know that some consultants are very strong on having a best practice that the data source variable in TI should be the same type as the source and then if you need a string variable for TI you declare an extra variable and use NumberToString, NumberToStringEx, etc. I have never really seen a need for this but this might be a case in point where it would be a good idea.