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
TI code for repeating element / values
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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);
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
www.infocat.co.uk
-
- 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
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!
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!
-
- MVP
- Posts: 3120
- 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
vAccountLastRow = '';
in the Prolog tab of the process.
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
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: 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
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!
"","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!
-
- 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
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
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