SUBST enters a comma in the middle of number

[tosca1978]

Hi all,

I have created a TI to bring in data via an ODBC link. The month variable looks like "201110.0000000" and fails to load to cube. So I have created a new month variable with SUBST(YYYYMM,1,5) to return 201110 which is a valid element in my Time dimension.

The TI runs with minor errors (although NO data loads) which looks like this from the error log file:

"201110.","CG001101","D.03.01","06028000","USD","41731500.",Data Source line (1) Error: Data procedure line (11): Invalid key: Dimension Name: "Time", Element Name (Key): "201,1"

I think that the SUBST is placing a "," in the middle of the number causing teh data not to load. I have tried adding a couple of new varaiables - 1st one to only return '201110' as a number, then 2nd one to turn it back into a string - as I thought this may help clear the "," but I have had no joy.

Can anyone help me!?

Many thanks!

[rmackenzie]

Assuming that YYYYMM is the name of your first variable, check what output you get if you use this formula:

Code: Select all

sPeriod=NumberToString(YYYYMM);

[tosca1978]

rmackenzie - I cannot thank you enough! NumbersToString worked a treat and my cube is now full of numbers!

I spent hours last night twaeking things to find a solution with no joy.

Thanks again!