Help with "If" Statement please...

Post Reply
Lorna
Posts: 15
Joined: Wed Jan 26, 2011 5:25 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Help with "If" Statement please...

Post by Lorna »

Hello.

I am very new to TM1 and I need some urgent assistance with an 'IF' statement in a TI Process.

Basically....

I have a cube that contains a kilometer reading per trip, per team, per region. Lets say the data looks like this:

Region A, Team 1, km.

I want to accumulate the km, per Team, per Region. If that Accumulated km is > 6250, then I use Rate 2 to calc the cost for the disctance over 6250.
If the Accumulated Cost is not more than 6250, I use Rate 1 to calc the cost for under 6250.

I am not sure how to do this. I was thinking (this come from days gone by Cobol Syntax...) I would use a variable vRegion and vTeam. Everytime I "read" the cube, I would check

Code: Select all

if Region is still = vRegion and Team is still = vTeam. (same team same Region)
 Accumkm = Accumkm + km.
 If Accumkm > 6250 then
    Difference = Accumkm - 6250
    Cost = Difference * Rate 2
 Else
    Cost = Km * Rate 1
 endif
Else
 vRegion = Region
 vTeam = Team
Endif
PS Why I test for Region AND Team to be equal - I might have a "Team 1" in Region ABC and Region DEF.

My Questions are...
1. I need help with the syntax of the "IF" Statement.
2. Help me understand pls...in the TI Code...does all the code get excecuted for every 'line' read in the cube?
3. If I need to initiate a variable once and once only - where do I do that?
4. If I need to initiate a variable for every read, do I just put that in the top of the TI Process?

ANY help will be appreciated thanx!
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: Help with "If" Statement please...

Post by Steve Vincent »

Hi Lorna,

You'll find a wealth of info in the FAQ including a section on TI.

http://forums.olapforums.com/viewtopic.php?f=3&t=1332

The syntax of TI is not the same as rules, and also differs from other languages like cobal or VBA. The code you have posted is far from correct for TI, later today (if i geta chance) i'll try to write a TI version and post it unless someone gets to it before me ;)

To answer a few of the queries tho;

2> No. Prolog is run once and never reads the datasource. Metadata and Data will read each line of source and execute their code for each one. Epilog only runs once and after all the other tabs have completed.

3> Prolog.

4> Depending on where you need to use it, either metadata or data tabs. If the value is required to amend a dimension in any way then metadata is where it should go. If it is for data loaded, then data.
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
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: Help with "If" Statement please...

Post by Alan Kirk »

Lorna wrote:Hello.

I am very new to TM1 and I need some urgent assistance with an 'IF' statement in a TI Process.

Basically....

I have a cube that contains a kilometer reading per trip, per team, per region. Lets say the data looks like this:

Region A, Team 1, km.

I want to accumulate the km, per Team, per Region. If that Accumulated km is > 6250, then I use Rate 2 to calc the cost for the disctance over 6250.
If the Accumulated Cost is not more than 6250, I use Rate 1 to calc the cost for under 6250.

I am not sure how to do this. I was thinking (this come from days gone by Cobol Syntax...) I would use a variable vRegion and vTeam. Everytime I "read" the cube, I would check

Code: Select all

if Region is still = vRegion and Team is still = vTeam. (same team same Region)
 Accumkm = Accumkm + km.
 If Accumkm > 6250 then
    Difference = Accumkm - 6250
    Cost = Difference * Rate 2
 Else
    Cost = Km * Rate 1
 endif
Else
 vRegion = Region
 vTeam = Team
Endif
PS Why I test for Region AND Team to be equal - I might have a "Team 1" in Region ABC and Region DEF.

My Questions are...
1. I need help with the syntax of the "IF" Statement.
2. Help me understand pls...in the TI Code...does all the code get excecuted for every 'line' read in the cube?
3. If I need to initiate a variable once and once only - where do I do that?
4. If I need to initiate a variable for every read, do I just put that in the top of the TI Process?

ANY help will be appreciated thanx!
A lot of this is covered in the TI manual but it does take a fair amount of time and effort to start to really get how TI works. (And I see that Steve posted something while I was typing, but I'll post this anyway since it's here.)

The first point I'd make is that I'm not sure that it's necessary to be doing this by TI anyway. TI would be useful if for some reason you want to create a static snapshot of costs at any given time. However from the way you describe the problem it would seem to be equally possible for you to create a rule which calculates the cost for each team / region combination given the sum of kilometres stored. Worth a thought, anyway.

But assuming that you do want to do this through TI here are the basic principles that you need to know:
  • All data sources, assuming that a process has one, are in the form of a table. That is, a sequence of rows containing the same number of columns with each column representing a characteristic of the data on the row. It doesn't matter whether it's a flat file, an ODBC connection or a cube view, this will be so. If it's a cube view then each column in the data source will represent a dimension in the source cube. Each row will represent a single value in the cube.
  • The execution flow of a TI process is as follows (this answers your second question):
    1. The prolog tab code executes once, when the process first triggers. This will run regardless of whether there is any data in your data source, or indeed whether there is a data source. Any constant value (and this answers question 3 of your post) should therefore be declared here. The value will be available on all of the following tabs.
    2. The Metadata tab code, if any, executes once for each row of data in your data source. This is generally used to update things like dimensions and their hierarchies, which it doesn't sound like you're doing.
    3. The Data tab code, if any, executes once for each row of your data source. This is where you update values in the cube. If you need to initialise a variable for each read, you do it at the top of this tab.
    4. The Epilog code executes once, after the last row of data has been dealt with, immediately prior to the end of the process.
  • If statements have two primary syntaxes, but for TI the block syntax is commonly used. They're are used to execute a block depending on whether a particular condition is true or false. Supposing that the variable Total_Kilometres was a variable in your data source (which I know it isn't at present, but suppose for a minute that it was) you would test to see whether the value for that variable is greater than (say) 6250. Let's also assume that you've declared Rate1 and Rate2 in your prolog. dbl_Cost is a further variable that you might later use to write to the cube via a CellPutN statement.

    Code: Select all

    # The first line is the logical test. If this is True
    # then the first block will be executed. If false, the 
    # second block will be. 
    # There is also a one line version of the If statement
    # which performs similarly but can execute only a single
    # statement for True or False conditions; see the
    # TM1 Reference Guide for details.
    # You can also test for multiple conditions using ElseIf.
    # The Else statement is optional.
    If ( Total_Kilometres > 6250);
        dbl_Cost = Total_Kilometres * Rate2;
    Else;
        dbl_Cost = Total_Kilometres * Rate1;
    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.
Lorna
Posts: 15
Joined: Wed Jan 26, 2011 5:25 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Help with "If" Statement please...

Post by Lorna »

Thank you so much for your feedback.

After reading all, I think that I would in fact, need to write a rule in stead of a TI Process, because this is something that would need to run "automatically" and not be started by a user or process.

Ok. Upon deciding to go the Rule way....how would I simulate that logic that I included in my original post? More specifically, how would I simulate this "running total" for the kilometers - I would need to set whatever variable (for the region and team) to Zero when these values change.

Thanx!
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: Help with "If" Statement please...

Post by tomok »

Based on the example given you would need at least four dimensions in your cube, Trip, Team, Region and Measure. At first glance I would say the Trip dimension would have a single parent called "All_trips" calculating the cost on this, letting the natural dimension hierarchy sum the number of miles in All_Ttrips for you, but that is going to cause all sorts of problems when writing the rule. Since you want your rate to be based on the accumulated mileage, which is a consolidated element, but by Team and Region, which are not consolidated elements, you would have to have a pretty convoluted rule. Instead, I would propose making the element "All_Trips" a leaf element and use a rule to sum it. A simple example rule would be (assuming you have items called "Miles'" and "Cost" in your Measure dimension):

['All_Trips','Cost']=N:
IF(DB(CubeName,'All_trips',!Team,!Region,'Miles')<6250,
DB(CubeName,'All_trips',!Team,!Region,'Miles') * 100,
DB(CubeName,'All_trips',!Team,!Region,'Miles') * 200),;

['All_Trips','Miles']=['Trip1','Miles']+['Trip2,'Miles']+['Trip3','Miles']+['Trip4','Miles'].......['Tripn','Miles']

You could get fancier and have the values 100 and 200 be stored in a cube somewhere (and have a DB reference to pull the values) or better yet, have the rates by Team and Region, by putting them into a lookup cube containing the Team and Region dimension. Also, don't forget ypu'll need to write feeder statements so that your Cost number can be summed by hiearchy in the Team and Region dimension.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Lorna
Posts: 15
Joined: Wed Jan 26, 2011 5:25 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

How to create a "Running Total" in TI Process

Post by Lorna »

Hi

I want to create a running total in a TI process, but I have some trouble doing that.

I have a kilometer reading, per line, that I'd like to accumulate. When the accumulation PER REGION (aregion) AND TEAMNUMBER (ateamno) gets over 6250, I want to do the cost calculation with another rate.

My code looks like this...

Code: Select all


#****Begin: Generated Statements***
temp=0;
#****End: Generated Statements****

temp =  CellGetN('atm test','Volume', abank, ateamno, acentre, teamtype, 'Totkm', aregion, adate     ....temp is the actual kilometer reading I want to accumulate with

IF (aregion @<> vregion &  ateamno @<> vteam);
   vkm = 0;
   vregion = aregion;
   vteam = ateamno;
ENDIF;
vkm = vkm + temp; ..... vkm is supposed to be the running total, but the value is always TWICE the vkm value - obviously  -):
IF (vkm > 6250);
               diffhigh = temp *  CellGetN('ATM Rates', 'Rate', abank, teamtype, 'Totkm > 6250', '2011 Annual');
               difflow = 0;
  else;
               diffhigh = 0;
               difflow = temp *  CellGetN('ATM Rates', 'Rate', abank, teamtype, 'Totkm', '2011 Annual');
  endif;

CellPutN (difflow, 'atm test', 'Cost',    abank, ateamno, acentre, teamtype, 'Totkm', aregion, adate);
CellPutN (diffhigh,'atm test', 'Cost 2', abank, ateamno, acentre, teamtype, 'Totkm > 6250', aregion, adate);

asciioutput ('aa.txt','vkm ', numbertostring(vkm), 'temp ', numbertostring(temp), aregion,ateamno);

The results of the asciioutput looks like this.

"vkm ","260","temp ","130","JOHANNESBURG","DC 1"
"vkm ","1668","temp ","834","JOHANNESBURG","DC 1"

The variables 'vteamno', 'vregion' and 'vkm' are also initialized in the Prolog area.

Any help would be appreciated!
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: How to create a "Running Total" in TI Process

Post by tomok »

Isn't there already a thread where this was asked and discussed at length?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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: How to create a "Running Total" in TI Process

Post by Alan Kirk »

tomok wrote:Isn't there already a thread where this was asked and discussed at length?
Yup, that's why they've been merged.
"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.
Lorna
Posts: 15
Joined: Wed Jan 26, 2011 5:25 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Help with "If" Statement please...

Post by Lorna »

Thank you so much for your help tomok!
Post Reply