How to reference data lines as variables in TI?

Post Reply
jwafro
Posts: 22
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 » Tue Jun 12, 2018 10:34 am

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: 1790
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

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

Post by Steve Rowe » Tue Jun 12, 2018 11:38 am

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

jwafro
Posts: 22
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 » Tue Jun 12, 2018 12:31 pm

Amazing, thank you!!

jwafro
Posts: 22
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 » Tue Jun 12, 2018 12:49 pm

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
Community Contributor
Posts: 227
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

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

Post by PavoGa » Tue Jun 12, 2018 2:02 pm

I think you want to look at NumberToStringEx().
Ty
Cleveland, TN

User avatar
tomok
MVP
Posts: 2474
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 » Tue Jun 12, 2018 2:50 pm

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: 22
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 » 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".

lotsaram
MVP
Posts: 3108
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Tue Jun 12, 2018 4:43 pm

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: 1790
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

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

Post by Steve Rowe » Tue Jun 12, 2018 5:31 pm

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...

User avatar
tomok
MVP
Posts: 2474
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 » Tue Jun 12, 2018 6:19 pm

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: 3108
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Tue Jun 12, 2018 8:31 pm

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