Page 1 of 1

How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 10:34 am
by jwafro
Hi All

I am attempting to automate a data load of curves into my model, which has potentially hundreds of columns of data.
Aside from initial data points, the naming convention TI has selected works for me ; eg Coll1 Coll2 .. Coll500 etc.
The cube I am loading to has a dimension with '1','2' .. etc.
For each curve, I was planning on looping through the dimension (1 to 500) and using the loop to determine the column to load from.

However, its not letting me do this, and errors at the point whereby I check the logical value of nInputColl

Is there any way to get TI to dynamically reference variables like what I am attempting to do?

Code: Select all

#Need to loop through the Collection Period Lines 

X = 1;

#There are 780 columns in the data extract that could be populated, but 'only' 500 Durations in collection period.
While( X <= 500);

     sInputColl = 'Coll' | numbertostring(X);
     nInputColl = sInputColl;

##ERRORS HERE##
IF ( nInputColl <> 0);

vIFRS9 = 'Performing';
                  if(CellIsUpdateable(pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9, numbertostring(X))=1);
                        CellPutN(nInputColl,pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9,numbertostring(X));
                 Endif;
vIFRS9 = 'Under Performing';
                  if(CellIsUpdateable(pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9, numbertostring(X))=1);
                         CellPutN(nInputColl,pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9, numbertostring(X));
                  Endif;
vIFRS9 = 'Not Performing';
                  if(CellIsUpdateable(pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9, numbertostring(X))=1);
                         CellPutN(nInputColl,pCubeTemplate,pVersion,sTermName,vNetGrossMetric,vIFRS9, numbertostring(X));
                  Endif;
Endif;




X = X +1 ;

End;

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 11:38 am
by Steve Rowe
You need to use Expand to translate your string reference to an indirect variable reference.

sInputColl = Expand('%Coll' | numbertostring(X) | '%');
#Expand returns a string (or do it all on a single line)
nInputColl = StringToNumber(sInputColl);


So if Coll10 contain the value 99, when X=10 nInputCol=99

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 12:31 pm
by jwafro
Amazing, thank you!!

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 12:49 pm
by jwafro
Having discovered Expand function, it certainly is very helpful.

Any suggestions on how to get around the truncation issue? my values are all decimal places eg 0.00123456, the process requires a higher degree of accuracy :D

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 2:02 pm
by PavoGa
I think you want to look at NumberToStringEx().

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 2:50 pm
by tomok
jwafro wrote: Tue Jun 12, 2018 12:49 pm Any suggestions on how to get around the truncation issue? my values are all decimal places eg 0.00123456, the process requires a higher degree of accuracy :D
What truncation issue would you be referring to? I didn't see anything in your post about a truncation issue. TM1 is going to load whatever it finds in the source file, there won't be any truncating. However, what you see in the cube may not "completely" reflect what is in the source file. This is because TM1 stores data in floating point form, which may not match the data type of your source file. It may be off a few decimal places here or there. That's just the way it is and there are numerous posts on this site about options for dealing with that.

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 3:56 pm
by jwafro
Truncation to do with using Expand

https://www.ibm.com/support/knowledgece ... xpand.html

That string has a fixed minimum length of 10 characters. If the converted number is too small to fill 10 characters, it is padded with leading spaces. Only three leading decimal characters are converted. For example, a numerical value of 0.123456789 is converted into the string "0.123".

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 4:43 pm
by lotsaram
jwafro wrote: Tue Jun 12, 2018 3:56 pm Truncation to do with using Expand

https://www.ibm.com/support/knowledgece ... xpand.html

That string has a fixed minimum length of 10 characters. If the converted number is too small to fill 10 characters, it is padded with leading spaces. Only three leading decimal characters are converted. For example, a numerical value of 0.123456789 is converted into the string "0.123".
Yes this is a big problem with Expand, ... a function that is otherwise so wonderful and useful in so many ways.
The only solution is to multiply all your input fields by a some factor to get around the 3 decimals issue and then divide again in your code to get back the the real value.

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 5:31 pm
by Steve Rowe
Interesting I had not noticed that issue, though I am nearly always using Expand with text rather than numbers. Thanks for raising.

Love the way that IBM document the defect so they don't have to treat it as a defect because it is documented, that seems to be a fairly recent addition to the documentation...

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 6:19 pm
by tomok
Aren't using expand to translate values from an input field into a variable name so you can update a cube. Why would you ever have a variable name with decimal places in it like "0.123456789"? The only thing I would see this being a problem is if you were using EXPAND to create an SQL string for inserting into a DB table.

Re: How to reference data lines as variables in TI?

Posted: Tue Jun 12, 2018 8:31 pm
by lotsaram
Tom read the technote. The issue is that Expand is really only designed for strings. So when it is used to read the value of a numeric variable there can be a loss of accuracy due to the string/numeric conversion.