While statements in TI process
-
- Posts: 4
- Joined: Tue Oct 14, 2008 3:54 pm
While statements in TI process
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
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
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: While statements in TI process
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.)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 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;
Last edited by Alan Kirk on Wed Oct 15, 2008 7:41 am, edited 1 time in total.
Reason: "An endless loop that runs forever" seemed a tad redundant. OK, more than a tad.
Reason: "An endless loop that runs forever" seemed a tad redundant. OK, more than a tad.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 4
- Joined: Tue Oct 14, 2008 3:54 pm
Re: While statements in TI process
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;
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;
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: While statements in TI process
Theeeere's your problem right there. Should be an ElseIf, not an If.robincollett wrote: If ( l_Period = 7);
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: While statements in TI process
Ooops, there's another one at period 10. By the way, you can simplify that if you like (also untested):Alan Kirk wrote:Theeeere's your problem right there. Should be an ElseIf, not an If.robincollett wrote: If ( l_Period = 7);
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;
Last edited by Alan Kirk on Wed Oct 15, 2008 9:13 am, edited 1 time in total.
Reason: Ooops, had an extra character after the 2007 in the second example.
Reason: Ooops, had an extra character after the 2007 in the second example.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 4
- Joined: Tue Oct 14, 2008 3:54 pm
Re: While statements in TI process
that is what you get for being lazy and copying and pasting in the first place.
Cheers,
Robin
Cheers,
Robin
-
- Posts: 51
- Joined: Thu Jul 24, 2008 6:14 am
Re: While statements in TI process
I'm sorry for bringing this up again, but I couldn't justify a new thread for a simple questionAlan 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 would have used this:
Code: Select all
s_Period = '2007' | NUMBERTOSTRINGEX(l_Period,'00','','');
- Steve Rowe
- Site Admin
- Posts: 2407
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: While statements in TI process
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,
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,
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: While statements in TI process
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: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.
This translates as "we forgot to put 'em in and can't be botered adding half a dozen extra topics now".Documentation Updates
The following items note corrections and additions to the TM1 manuals and online help.
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: While statements in TI process
Well i'll be...never knew that existed, cheers!
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
-
- Posts: 125
- Joined: Tue Jun 02, 2009 7:05 pm
- OLAP Product: TM1, PAX, PAW, SPSS
- Version: 2.0.916.10 on RHEL
- Excel Version: 2016
- Location: Minneapolis, MN
Re: While statements in TI process
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'
Example 8 character fixed: '123' to '00000123' and '1234' to '00001234'
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: While statements in TI process
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: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'
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;
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 125
- Joined: Tue Jun 02, 2009 7:05 pm
- OLAP Product: TM1, PAX, PAW, SPSS
- Version: 2.0.916.10 on RHEL
- Excel Version: 2016
- Location: Minneapolis, MN
Re: While statements in TI process
Ah, thanks Alan. Beats a string of if statements for sure. Pardon the pun.
-
- MVP
- Posts: 3098
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: While statements in TI process
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;
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 2
- Joined: Wed Dec 01, 2010 8:58 pm
- OLAP Product: TM1
- Version: 9.4.1 and 9.5.1
- Excel Version: 2003
Re: While statements in TI process
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;
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;
-
- MVP
- Posts: 2831
- 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: While statements in TI process
Just nest your WHILE loops. Something along the lines of: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.
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;
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: While statements in TI process
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.
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.
Ankur Jain
-
- Posts: 2
- Joined: Wed Dec 01, 2010 8:58 pm
- OLAP Product: TM1
- Version: 9.4.1 and 9.5.1
- Excel Version: 2003
Re: While statements in TI process
Thanks you two! This does work for what I needed. My imagination was not working.