Counter in while loop

[Michael Barker]

Hi all,

I have a large number of elements I want to add to a dimension in TI,

i.e.
DimensionElementInsert('Department','',Department_1,'S');
DimensionElementInsert('Department','',Department_2,'S');
DimensionElementInsert('Department','',Department_3,'S');
DimensionElementInsert('Department','',Department_4,'S');
DimensionElementInsert('Department','',Department_5,'S');
(etc.)

Is it possible to create a counter (i) that I can substitute into the department variables (e.g. Department_i) with a WHILE loop?

Thanks in advance.

[Catherine]

Hi,

Yes, it is. That would be something like that (not tested...):

i=1;
While (i<=200);
DimensionElementInsert('Department','','Department_'|i,'S');
i=i+1;
end;

Your element names would then be:
Department_1
Department_2
...
Department_200

Is that what you're looking for?

HTH

[Michael Barker]

Hi Catherine,

I'm referencing Department_1, Department_2, etc. directly as variables.

Enclosing them in inverted commas populates the dimension with the text within. I'm after the variables values. So I cannot use the inverted commas.

Thanks.

[declanr]

Hi,

Yes, it is. That would be something like that (not tested...):

i=1;
While (i<=200);
DimensionElementInsert('Department','','Department_'|i,'S');
i=i+1;
end;

Your element names would then be:
Department_1
Department_2
...
Department_200

Is that what you're looking for?

HTH

Catherine has answered the question but I think the code needs just 1 small tweak, in the case above i is a numeric term so when stating 'Department_' | i the code may not work due to i not being string as such it could simply be replaced with 'Department_' | NumberToString ( i )

e.g.

i=1;
While (i<=200);
DimensionElementInsert('Department','','Department_'|NumberToString ( i ) ,'S');
i=i+1;
end;

[declanr]

Hi Catherine,

I'm referencing Department_1, Department_2, etc. directly as variables.

Enclosing them in inverted commas populates the dimension with the text within. I'm after the variables values. So I cannot use the inverted commas.

Thanks.

If Department_1 and Department_2 are variables then why don't you just populate in the metadata tab with a simple

DimensionElementInsert( 'Dim', '', v1, 'S');

Or do you mean that they are the names of the variables as opposed to the variable values?

[Michael Barker]

They are variable names. There's s lot of them. I can copy/paste the code for all of them in the metadata tab, but that just seems unnecessary.

[declanr]

I'm not sure I entirely understand the problem.

Could you provide an example csv of the datasource and explain what you want to be created?

[Grandryjn]

I think that you want to retrieve the variable name from the concatenation of 'Departement_' and a number i in your while.

I think that you should try the "Expand" function.

Something like that "expand('%Departement_'|numbertostring(i)|'%')" should give you the variable value.

Is that correct?

[Michael Barker]

Expand() is what I was looking for. Let me try it out.

Thanks!

[Grandryjn]

Tell me if it worked

[mykill]

It should be a double Expand. The inner one to get the name and the outer one to get the value.

[Steve Rowe]

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

A few examples here on the use of expand to manipulate variable names.

I think you only need a single expand.

Double expand is when the value you need is held in a variable that is itself the value of antother variable....

[Michael Barker]

Thanks all, works fantastically.

Code: Select all

WHILE()

j = NUMBERTOSTRING(i);

DIMENSIONELEMENTINSERT('Department','',EXPAND('%Department_'|EXPAND('%j%')|'%'),'S');

i = i +1

END

[Steve Rowe]

Except you only need on expand

DIMENSIONELEMENTINSERT('Department','',EXPAND('%Department_'| j|'%'),'S');

will work too