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.

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

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.

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.

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.

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.