While statements in TI process

Post Reply
robincollett
Posts: 4
Joined: Tue Oct 14, 2008 3:54 pm

While statements in TI process

Post by robincollett »

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
User avatar
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

Post by Alan Kirk »

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.
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.
"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.
robincollett
Posts: 4
Joined: Tue Oct 14, 2008 3:54 pm

Re: While statements in TI process

Post by robincollett »

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;
User avatar
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

Post by Alan Kirk »

robincollett wrote: If ( l_Period = 7);
Theeeere's your problem right there. Should be an ElseIf, not an If.
"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.
User avatar
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

Post by Alan Kirk »

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;
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.
"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.
robincollett
Posts: 4
Joined: Tue Oct 14, 2008 3:54 pm

Re: While statements in TI process

Post by robincollett »

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

Cheers,

Robin
Lukas Meyer
Posts: 51
Joined: Thu Jul 24, 2008 6:14 am

Re: While statements in TI process

Post by Lukas Meyer »

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 :)
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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,
Technical Director
www.infocat.co.uk
User avatar
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

Post by Alan Kirk »

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.
"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.
User avatar
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

Post by Steve Vincent »

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
image2x
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

Post by image2x »

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'
User avatar
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

Post by Alan Kirk »

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.)
"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.
image2x
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

Post by image2x »

Ah, thanks Alan. Beats a string of if statements for sure. Pardon the pun.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
StellaStrickland
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

Post by StellaStrickland »

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;
tomok
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

Post by tomok »

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;
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ajain86
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

Post by ajain86 »

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.
Ankur Jain
StellaStrickland
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

Post by StellaStrickland »

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