TI code for repeating element / values

Post Reply
ngphong
Posts: 7
Joined: Thu Jun 22, 2017 5:17 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

TI code for repeating element / values

Post by ngphong »

Hi, I am using Perspectives, version 10.2.2. I am working on a data file that has blank / repeating value on the Expense Account column, see example below. I would like to write a TI code to have those blank rows to have the same value as the row above, until it see a new value, i.e. from Feb-2018 to Dec-2018, the value of the Expense account will be 701010 until to it comes to account 712100, then Feb-2018 to Dec-2018 of the rows below will be 712100. Would you help, please?

Expense Book
Account Period Name FIN CNF US
------------ ------------------ ------------------
701010 JAN-2018 177.08
FEB-2018 177.08
MAR-2018 177.08
APR-2018 177.08
MAY-2018 177.08
JUN-2018 177.08
JUL-2018 177.08
AUG-2018 177.08
SEP-2018 177.08
OCT-2018 177.08
NOV-2018 177.08
DEC-2018 177.08
Account 701010 ------------------
Totals: 2,124.96

712100 JAN-2018 25,685.26
FEB-2018 25,685.22
MAR-2018 25,539.31
APR-2018 25,539.31
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: TI code for repeating element / values

Post by Steve Rowe »

Have a variable called
vAccountThisRow
this holds the account for the row of data you are processing.

At the end of the data tab say
vAccountLastRow=vAccountThisRow;

At the start of the data tab say

vAccountThisRow=If (vAccountThisRow@='' , vAccountLastRow , vAccountThisRow);
Technical Director
www.infocat.co.uk
ngphong
Posts: 7
Joined: Thu Jun 22, 2017 5:17 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

Re: TI code for repeating element / values

Post by ngphong »

Thank you so much Steve!

Sorry for being so new at TI coding. It gives me an error that I also need to define vAccountLastRow. I think that because the code
vAccountThisRow=If (vAccountThisRow@='' , vAccountLastRow , vAccountThisRow);
is at the start of the data tab.

Thanks again!
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: TI code for repeating element / values

Post by Wim Gielis »

vAccountLastRow = '';

in the Prolog tab of the process.
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
ngphong
Posts: 7
Joined: Thu Jun 22, 2017 5:17 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

Re: TI code for repeating element / values

Post by ngphong »

Thanks Wim. When I set vAccountLastRow = '' in the Prolog, now all rows have blank in the account columns. Below is the asciioutput file.
"","t "," ","Period N","ame "," FIN CNF US"
"","----","-- ","--------","----------"," ------------------"
""," "," ","JAN-2018"," "," 177.08"
""," "," ","FEB-2018"," "," 177.08"
""," "," ","MAR-2018"," "," 177.08"
""," "," ","APR-2018"," "," 177.08"
""," "," ","MAY-2018"," "," 177.08"
""," "," ","JUN-2018"," "," 177.08"
""," "," ","JUL-2018"," "," 177.08"
""," "," ","AUG-2018"," "," 177.08"
""," "," ","SEP-2018"," "," 177.08"
""," "," ","OCT-2018"," "," 177.08"
""," "," ","NOV-2018"," "," 177.08"
""," "," ","DEC-2018"," "," 177.08"
""," "," ","Account ","701010 "," ------------------"
""," "," ","Totals: "," "," 2,124.96"
"","","","","",""
""," "," ","JAN-2018"," "," 25,685.26"
""," "," ","FEB-2018"," "," 25,685.22"
""," "," ","MAR-2018"," "," 25,539.31"

But I think I can play around with the advised code to make it works. Thank you so much!
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TI code for repeating element / values

Post by Mark RMBC »

Just an observation,

How have you set the initial value for vAccountThisRow? I would have thought at the start of the data tab you need to set vAccountThisRow = vAccount.

I ask this because I am wondering given the codes above if vAccountThisRow is ever not blank!

cheers, Mark
Post Reply