While statements in TI process

[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

[Alan Kirk]

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.

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

[Alan Kirk]

If ( l_Period = 7);

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

[Alan Kirk]

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;


[robincollett]

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

Cheers,

Robin

[Lukas Meyer]

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

[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,

[Alan Kirk]

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.

[Steve Vincent]

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

[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'

[Alan Kirk]

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

[image2x]

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

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