TM1 Forum

Posted: **Wed Oct 15, 2008 6:46 am**

Hi,

I have a cube with several dims and need to post values against some elements in these dimensions but across multiple periods as part of a TI Process. I therefore have had it suggested that using a while function and looping it might work.

Does anyone has an example of what the code is for this to help me out

Cheers

Robin

Posted: **Wed Oct 15, 2008 7:40 am**

robincollett wrote:Hi,

I have a cube with several dims and need to post values against some elements in these dimensions but across multiple periods as part of a TI Process. I therefore have had it suggested that using a while function and looping it might work.

Does anyone has an example of what the code is for this to help me out

The general syntax is pretty straightforward. Usually you specify a maximum value, and a variable that increments with each loop until it reaches that value. (That part's very, VERY important; if you forget to increment you end up with a loop that runs forever. You generally won't forget more than once or twice, I guarantee it.)

The following is untested air code, but it could go something like this. Let's assume that this loop is being done in the Data tab for each row of data from your data source. We'll also assume that V0, V1 and V2 are variables relating to the first three dimensions of your 4 dimensional cube, and that the period is the last dimension :

Code: Select all

l_PeriodMax = 12;
l_PeriodIdx = 1;

While ( l_PeriodIdx <= l_PeriodMax);

   If (  l_PeriodIdx = 1);
       s_Period = 'Jan'); 
   ElseIf (  l_PeriodIdx = 2);
       s_Period = 'Feb'); 
   ElseIf (  l_PeriodIdx = 3);
       s_Period = 'Mar'); 
   EndIf;
# You get the idea; I don't need to do all 12. Essentially s_Period
# is assigned the name of an element in your period dimension.

   CellPutN(Value, 'MyCube', V0, V1, V2, s_Period);

   l_PeriodIdx = l_PeriodIdx + 1;

End;

The If statement is one possibility. If you're writing to elements of a period consolidation, you could get the children of that consolidation and use THEM as the s_Period values. In this case you'd get l_PeriodMax from the rules ElCompN function instead of hard coding it. Or perhaps you may need to use a lookup cube to get the s_Period value; it just depends on the nature of the source data and how you need to translate it into the periods that you want to write to. You're only limited by imagination with this kind of thing.

Posted: **Wed Oct 15, 2008 9:01 am**

Hi Alan,

cheers for that - I get the feeling that this is going to be a really basic question but when I amend that for my cube it says the "end" is out of place in an error message. Do you know where it shoudl go or why it would be wrong - I have the below code in place

V11='All Swaps';
V12=if(V3@='','',V3);
V13=subst(start,7,4)|subst(start,4,2);
V14=subst(finish,7,4)|subst(finish,4,2);
V15=if(V13@<'200800','200800',subst(ATTRS('PERIOD',V13,'CAL'),3,6));
V16=if(V14@<'200800','200800',subst(ATTRS('PERIOD',V14,'CAL'),3,6));
V17=v16;

l_PeriodMax = 12;
l_Period = 1;

While ( l_Period <= l_PeriodMax);

If ( l_Period = 1);
s_Period = '200701';
ElseIf ( l_Period = 2);
s_Period = '200702';
ElseIf ( l_Period = 3);
s_Period = '200703';
If ( l_Period = 4);
s_Period = '200704';
ElseIf ( l_Period = 5);
s_Period = '200705';
ElseIf ( l_Period = 6);
s_Period = '200706';
If ( l_Period = 7);
s_Period = '200707';
ElseIf ( l_Period = 8);
s_Period = '200708';
ElseIf ( l_Period = 9);
s_Period = '200709';
If ( l_Period = 10);
s_Period = '200710';
ElseIf ( l_Period = 11);
s_Period = '200711';
ElseIf ( l_Period = 12);
s_Period = '200712';
Endif;
CellPutN(V6,'swap',s_Period,V1, 'Interest Rate');
CellPutN(V9,'swap',s_Period,V1, 'Swap Rate');
CellPutN(Volume,'swap',s_Period,V1, 'amount');

l_Period = l_Period + 1;

End;

Posted: **Wed Oct 15, 2008 9:06 am**

robincollett wrote: If ( l_Period = 7);

Theeeere's your problem right there. Should be an ElseIf, not an If.

Posted: **Wed Oct 15, 2008 9:12 am**

Alan Kirk wrote:
robincollett wrote: If ( l_Period = 7);
Theeeere's your problem right there. Should be an ElseIf, not an If.

Ooops, there's another one at period 10. By the way, you can simplify that if you like (also untested):

Code: Select all

 
If( l_Period < 10);
   s_Period = '20070' | Str (l_Period, 1, 0);
Else;
   s_Period = '2007' | Str (l_Period, 2, 0);
EndIf;

Posted: **Wed Oct 15, 2008 9:32 am**

that is what you get for being lazy and copying and pasting in the first place.

Cheers,

Robin

Posted: **Sat Oct 18, 2008 7:33 am**

Alan Kirk wrote:
Ooops, there's another one at period 10. By the way, you can simplify that if you like (also untested):
Code: Select all
 
If( l_Period < 10);
   s_Period = '20070' | Str (l_Period, 1, 0);
Else;
   s_Period = '2007' | Str (l_Period, 2, 0);
EndIf;

I'm sorry for bringing this up again, but I couldn't justify a new thread for a simple question

I would have used this:

Code: Select all

 s_Period = '2007' | NUMBERTOSTRINGEX(l_Period,'00','','');

Is this bad? I don't see people using this construct at all... so it has to be bad, right

Posted: **Sun Oct 19, 2008 7:09 am**

Nice use of the function.

My guess as to why no one suugested it, is that NUMBERTOSTRINGEX is a fairly recent addition to the TI function set?

Not sure, anyway I'll certainly be using it, it's a neat way around a problem I hit many times.

Cheers,

Posted: **Sun Oct 19, 2008 8:35 pm**

Steve Rowe wrote:Nice use of the function.
My guess as to why no one suugested it, is that NUMBERTOSTRINGEX is a fairly recent addition to the TI function set?
Not sure, anyway I'll certainly be using it, it's a neat way around a problem I hit many times.

It dates back to 8.2, but doesn't appear even in the 8.2.12 help file; it's in the 8.2 Release Notes as:

Documentation Updates
The following items note corrections and additions to the TM1 manuals and online help.

This translates as "we forgot to put 'em in and can't be botered adding half a dozen extra topics now".

To be honest the reason that I didn't mention it is simply that it had slipped my mind; it's not one that I've used very often since most of the time I want exports to be raw data (for importing into Access, Excel, etc) rather than formatted data. However it definitely would be a plus for element evaluation and log file exports.

Posted: **Tue Oct 21, 2008 7:29 am**

Well i'll be...never knew that existed, cheers!

Posted: **Sat Dec 26, 2009 6:50 pm**

So this neat for right padding, but does anyone have any tricks for easily left-padding a string of varying initial length?

Example 8 character fixed: '123' to '00000123' and '1234' to '00001234'

Posted: **Sat Dec 26, 2009 7:51 pm**

image2x wrote:So this neat for right padding, but does anyone have any tricks for easily left-padding a string of varying initial length?

Example 8 character fixed: '123' to '00000123' and '1234' to '00001234'

This probably should have been a separate topic, but since we're already here, the following should do it for you. Supposing that you already have the number as a string:

Code: Select all

# Example value
s_NumberAsString = '123';

# Ensure that there are no errors if the
# original string is too long.

If ( Long ( s_NumberAsString ) < 8 );

    s_PaddedNumber = 
      Subst ( '00000000', 1, 8 - Long ( s_NumberAsString ) ) 
      | s_NumberAsString ;

Else; 

    s_PaddedNumber = 
      s_NumberAsString ;

EndIf;

Change the 8 to whatever you want the final string length to be. (Or better still, define it as a variable so that you can change it as needed.)

Posted: **Sat Dec 26, 2009 8:06 pm**

Ah, thanks Alan. Beats a string of if statements for sure. Pardon the pun.

Posted: **Sat Dec 26, 2009 9:05 pm**

Or:

Code: Select all

If ( Long ( s_NumberAsString ) < 8 );
    s_PaddedNumber = FILL('0', 8 - Long ( s_NumberAsString ) ) | s_NumberAsString ;
Else; 
    s_PaddedNumber = s_NumberAsString ;
EndIf;

Posted: **Thu Dec 02, 2010 2:45 pm**

This works great for posting to several periods. I was wondering if there is a way to do the same thing for other dimensions. I am trying to post one number to every month and every manufacturing plant in a cube. I would like to only have one cellputN and somehow have it cycle through all the plants but can not figure out exactly how to do that. I thought about the Ellev and Attrn rules functions but can not figure out how or if they would work.

Thanks!

Currently I am doing the following:

vfp = vYear | '.01';

vPeriodIndx = 1;

### Loop through months to add data to all months of the year

WHILE (vPeriodIndx<=12);

CellPutN(Greenville,'UPLIFT','Left_to_Forecast','base list',Brand,'Greenville',vVers,Metaseries,vfp,'uplift %');
CellPutN(Berea,'UPLIFT','Left_to_Forecast','base list',Brand,'Berea',vVers,Metaseries,vfp,'uplift %');
CellPutN(Craigavon,'UPLIFT','Left_to_Forecast','base list',Brand,'Craigavon',vVers,Metaseries,vfp,'uplift %');
CellPutN(SN,'UPLIFT','Left_to_Forecast','base list',Brand,'SN',vVers,Metaseries,vfp,'uplift %');
CellPutN(Nijmegen,'UPLIFT','Left_to_Forecast','base list',Brand,'Nijmegen',vVers,Metaseries,vfp,'uplift %');
CellPutN(Irvine,'UPLIFT','Left_to_Forecast','base list',Brand,'Irvine',vVers,Metaseries,vfp,'uplift %');
CellPutN(Masate,'UPLIFT','Left_to_Forecast','base list',Brand,'Masate',vVers,Metaseries,vfp,'uplift %');
CellPutN(Modena,'UPLIFT','Left_to_Forecast','base list',Brand,'Modena',vVers,Metaseries,vfp,'uplift %');
CellPutN(Z,'UPLIFT','Left_to_Forecast','base list',Brand,'Z',vVers,Metaseries,vfp,'uplift %');

vPeriodIndx = vPeriodIndx + 1;

IF (vPeriodIndx <10);
vfp = vYear | '.0' | str (vPeriodIndx,1,0);

ELSE;
vfp = vYear | '.' | str (vPeriodIndx,2,0);

ENDIF;

END;

Posted: **Thu Dec 02, 2010 3:02 pm**

StellaStrickland wrote:This works great for posting to several periods. I was wondering if there is a way to do the same thing for other dimensions. I am trying to post one number to every month and every manufacturing plant in a cube. I would like to only have one cellputN and somehow have it cycle through all the plants but can not figure out exactly how to do that. I thought about the Ellev and Attrn rules functions but can not figure out how or if they would work.

Just nest your WHILE loops. Something along the lines of:

x =1;
y = 1;
WHILE(x <= DIMSIZ('Time');
TimeEl = DIMNM('Time',x);
WHILE(y <= DIMSIZ('Plant', y);
PlanEl = DIMNM('Plant',y);
IF(CellIsUpdateable(Cube,TimeEl,PlantEl)=1);
CellPutN(Value,Cube,TimeEl,PlantEl);
ENDIF;
y = y + 1;
END;
x = x + 1;
END;

Posted: **Thu Dec 02, 2010 3:23 pm**

Stella,

It seems the manufacturing plant value is determined by the rows, if so then you already have the proper implementation of looping through the periods and having a separate cellputn for each plant.

When you say "one number", is that the same number being posted to all manufacturing plants across all periods?
If so, then tomok's suggestion would work.

Posted: **Thu Dec 02, 2010 4:59 pm**

Thanks you two! This does work for what I needed. My imagination was not working.

TM1 Forum

While statements in TI process

While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process

Re: While statements in TI process