How to reference data lines as variables in TI?

Post Reply
jwafro
Posts: 25
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

How to reference data lines as variables in TI?

Post 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;
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

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

Post 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
Technical Director
www.infocat.co.uk
jwafro
Posts: 25
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

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

Post by jwafro »

Amazing, thank you!!
jwafro
Posts: 25
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

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

Post 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
User avatar
PavoGa
MVP
Posts: 612
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

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

Post by PavoGa »

I think you want to look at NumberToStringEx().
Ty
Cleveland, TN
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

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

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
jwafro
Posts: 25
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

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

Post 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".
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

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

Post 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...
Technical Director
www.infocat.co.uk
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

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

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply