Indirect variables in TI.

[Steve Rowe]

I've not see this raised as a technique any where, it would be appear to be a pretty powerful approach to the generalisation of TIs.

For a while now I've been trying to figure out a way of using something like excels Indirect function in TI and today myself and a colleague, Brendan O'Brien, cracked it. Once you figure it out it's a pretty obvious solution but I've never seen it publicised anywhere so I thought I'd post it, given the nature of TM1 though I'm sure someone will be along to say "I've been doing that for years, I thought everyone knew about this"

Anyway so the (simplified) problem is like this.
My TM1 consolidation system has multiple data feeds and I'm not able to influence the structure of the incoming flat files. Each flat file has the columns in a different order.
They all go into the same cube but I don't want to want to have to write and maintain multiple TIs for each of the structurally different flat files.

When I load the file I need to know which field Cost Centre is in so that I can write the data to the cube, but cost centre could be in any of the fields of the incoming file. I want to be able to do this in 1TI without writing a long IF statement, so I could write something like this to solve the issue

If entity='A';
CostCentre=Field3;
ElseIf entity='B';
CostCentre=Field4;
etc for all the many different entites I need to cope with;
EndIf;

Field3 and Field4 etc are the variables names in the TI relating to each column of the incoming file.

This is not generic however, I would need to edit the TI every time a new entity came on line.

What I wanted to do is to be able to populate a file properties cube so that I can say that in the files for entity A cost centre is Field 3 and for entity B the cost centre is in Field 4.

So in my file properties cube I say
.............Cost Centre
Entity A Field3
Entity B Field4

In the TI it's easy for me to retrieve that Field number as a string.

sFieldRef=CellGetS('Datasource_Properties' , 'Entity A' , 'Cost Centre');

So sFieldRef now contains the name of the TI variable that contains the value that I need to use for Cost Centre.

If I write

sCostCentre = sFieldRef;

Then I just make sCostCentre = 'Field3' which is not what I was after.

Reading the help it would seem that the Expand function ought to solve the problem

sCostCentre = Expand('%sFieldRef%');

This still returns sCostCentre = 'Field3' which close reading of the help is the correct behaviour.

Now comes the sneaky bit! If you nest the Expand statements

sCostCentre = Expand ( '%' | Expand ( '%sFieldRef%') | '%');

You get exactly the behaviour we expect, i.e. sCostcentre=Whatever the value that is in the variable Field3.

In some situations where you are trying to build complex and flexible TI processes I expect this to be a powerful technique.

Hopefully I've explained this so it makes sense!

Cheers,

[Martin Ryan]

We need a "like" button, ala facebook, that's a great idea.

[ajain86]

Your discovery gave me an idea.

I always hate writing a long string of IF statements when I need to check something in variables in a sequence ( v1, v2, v3, v4, v5, v6, v7, and so on...).

I used your idea to make it simpler.

nNum = 1;
While( nNum <= 7 );
sNum = NumberToString( nNum );
IF( EXPAND( '%v' | EXPAND( '%sNum%' ) | '%' ) @= 'Test' );
....
ENDIF;

nNum = nNum + 1;
END;


Thanks for sharing.

[David Usherwood]

@Steve, cast your mind back to the original generic version rollover code for our Newport project. Couldn't this technique be relevant for that kind of task? Suppose you'd still need the cellputn for each dimension count.

[Steve Rowe]

Ankur,
Yes also a good use of this approach, already used a few times yesterday in the TIs I'm writing.

David, I can't recall that I'm afraid, it was over 5 years ago I guess the answer would be probably.

[bskalli]

Hi Steve,

we must be patient in our forum.
I have a similar question one year ago.
Unfortunately, I have used the word Activation This Word can still be found in other
Programming languages ​​(APL, PL1 ..).
Within a week I see your solution under 2 topics.
In my case I have a ascii-file with 60 variables. I stored every helpfull Information in a dimension with attributes.
And now i just need a While + your Expand.

Thanks
Brahim

[Christopher Kernahan]

Steve,

Just wanted to add my thanks, you've saved me from pulling out all of my hair.

[Steve Rowe]

I've been meaning to post an update on this.
Ankur posted a use of the nested expand as shown below.

nNum = 1;
While( nNum <= 7 );
sNum = NumberToString( nNum );
IF( EXPAND( '%v' | EXPAND( '%sNum%' ) | '%' ) @= 'Test' );
....
ENDIF;

nNum = nNum + 1;
END;

In this example we don't need to nest the expand as we are only trying to get to the value of a variable (not the value of a variable the identifier of which is itself stored in another variable).

For example to add elements to a dimension in a nice tidy TI sequence.

Code: Select all

sElName1='Destination Instance';
sElName2='Destination Cube';
sElName3='Include ruled items';
sElName4='Refresh Dimensions';
sElName5='Rebuild Dimensions';
sElName6='Recreate Cube';


ixDim=1;
sDimName=sDimName17;
While ( ixDim<=5);
   sElName=Expand ('%sElName' | numbertostring(ixDim) | '%');
   If (Dimix ( sDimName , sElName )=0);
       DimensionElementInsert (sDimName , '', sElName , 'S' );
   EndIf;
  ixDim=ixDim+1;
End;

The help section on Expand appears to have sent many people down a bit a of a blind alley in that it implies that it is only for ODBC statements and so on. It is actually much more powerful even without nesting it.

Cheers,

[cfm04]

During your example you checked values in the If clause.
But did you try to assign values like this?

Code: Select all

ixDim=1;

While ( ixDim<=100);
   Expand ('%sElName' | numbertostring(ixDim) | '%') = numbertostring(ixDim);
     ixDim=ixDim+1;
End;

Thanks
CFM04

[Steve Rowe]

I've never been able to get dynamic variable names to work on the LHS of statements.

Don't forget though that you could build a cube and tear it down on the fly, cubes in this context behave pretty much like an array variable and in a CellPutN you can manipulate the element references pretty much how you like.

http://www.tm1forum.com/viewtopic.php?f=21&t=2691

Cheers,

[cfm04]

thanks for the hint.

[Ron Raetz]

This works. I really don't understand the Expand statement, but I followed the example and it did exactly what I wanted. I turned two pages of code into about 10 rows.

At the next TM1 Hall of Fame Induction Ceremony this guy should be featured.

[Alan Kirk]

This works. I really don't understand the Expand statement, but I followed the example and it did exactly what I wanted. I turned two pages of code into about 10 rows.

At the next TM1 Hall of Fame Induction Ceremony this guy should be featured.

It's Steve, he was inducted years ago.

Expand really isn't very well explained in the manual and IMHO it's not exceptionally well named either. Had I been writing it today I may well have called it something like "Extract" since its function is to extract a value from a variable.

Once you understand that it's relatively easy to use.

(a) A variable is a named "box" in memory which stores a value.
(b) If you know the name of the box in advance, then you have no problem.
(c) The problem arises when you want to figure out the variable name that you need to use based on a loop, or based on other inputs.
(d) For example, suppose that your data source has the variables named vnValue1 to vnValue10, and you don't want to hard code 10 separate lines in your TI to process the values in those 10 variables. Instead you just want to loop from 1 to 10.
(d) It's easy to write a loop which counts from 1 to 10, then just sticks that number on the end of the string 'vnValue' so that on the first loop you get 'vnValue1', on the second loop you get 'vnValue2', on the third one you get 'vnValue3' and so on.
(e) However that just gives you the variable name. To get to what is stored inside that "box", you need a function which takes the variable name, and returns (extracts) what is stored in it. That function is Expand().

You can think of it as drilling into a variable to find out what that variable contains.

[Bishop]

To build a practical example upon Alan's info, I've used this technique to create a dynamic generic process that will find the number of (total or subset) fields in an extract and loop thru them without needing to know the extract name. This is also extremely useful to check for a set of required fields in the extract and send out error notifications.

I needed a way to get the number of fields in a source extract and loop thru those fields. More specifically, we get an accounts property feed containing a number of variables that define a hierarchy structure that we rebuild daily. Our problem is that sometimes this hierarchy structure will change (i.e. the number of these fields will expand/contract) and require code changes. Because we're a larger company and running in a production environment, these changes involve change management, incident tickets and whatnot. My batch file [FieldCount.bat] simply does a regex pattern match on a source extract (using a field name pattern for a subset of fields, or a delimiter pattern to get the total number of fields) and dumps that number out to a text file. My generic TI [UTILITY - GetFieldCount.pro] reads in the text file as a source (one of the few ways I found to pass batch results back to a TI) and passes the number back to the parent TI that executes it using NumericGlobalVariable('DatasourceFieldCount'). So Rather than hard coding a hierarchy structure based uon a set number of fields, my code will dynamically handle the changes.

While they may not be the most succinct pieces of code, they tend to work quite well.

Parent TI PROLOG & METADATA code snippets
This makes use of the two attachments (included as .txt. files):

1. FieldCount.bat
2. UTILITY - GetFieldCount.pro

Code: Select all

##################
###   PROLOG   ###
##################
	# =================================================================================================== #
	# Get a count of the number of field names that match the regular expression pattern
	# =================================================================================================== #
	# Define a global variable to contain the value (only way to pass info between from a sub-process)
	NumericGlobalVariable('DatasourceFieldCount');

	# Run the sub process to get the count
	ExecuteProcess('UTILITY - GetFieldCount', 'pSource', sSource, 'pPattern',  sPattern , 'pFieldCountFile', sFieldCountFile );

##################
###  METADATA  ###
##################
	# =====================================================================================
	# Add C-Level consolidation elements to the dimension using the DEPTH#_SYS_NAME field values
	# =====================================================================================
	# Loop through the possible DEPTH_SYS_NAMEs
	idx = 1;
	WHILE( idx <=  DatasourceFieldCount );
		# Build the DataSource field name
		sFieldName = 'DEPTH' | NumberToString(idx) | '_SYS_NAME';

		# Get the field value in the DataSource INDIRECTLY using the variable containing the field name
		sFieldNameValue = TRIM(Expand ('%' | sFieldName | '%'));

		# Add consolidation element to the dimension as 'C' level if it does not already exist in the dimension and  DEPTH_SYS_NAME value in the DataSource is not blank
		IF( DIMIX( pDimName, sChartPrefix | sFieldNameValue) = 0 & LONG(sFieldNameValue) > 0); 
			DimensionElementInsert( pDimName, '', sChartPrefix | sFieldNameValue, 'C' ); 
			AsciiOutput( sLogFile, 'Added C-Level account: ' | sChartPrefix | sFieldNameValue );
		ENDIF;
		idx = idx + 1;
	End;