Page 1 of 1
Loading many measures
Posted: Mon Mar 04, 2013 5:45 pm
by rollo19
Close but no cigar.. any ideas how to get the value in the source file?
I had too many measures to define so decided to loop it.. it works except for what's highlighted below:
iMeasure = DIMSIZ('ABM Measures');
i=1;
While (i<=iMeasure);
vElement = DIMNM('ABM Measures',i);
vValue = ?the value matching the name of vElement in the source data file?
IF(vElement @<> 'Expense' % vElement @<> 'Revenue');
IF(CellIsUpdateable('ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement)=1);
nGetVal = CellGetN('ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement);
CellPutN(nGetVal + vValue, 'ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement);
ENDIF;
ENDIF;
i=i+1;
END;
Re: Loading many measures
Posted: Mon Mar 04, 2013 8:05 pm
by Wim Gielis
AFAIK, this isn't possible in TM1.
An IF structuur with multiple ELSEIF's will be your best bet.
For the future, can you please use
Code: Select all
tags to format your code here? Thanks.
Re: Loading many measures
Posted: Mon Mar 04, 2013 8:50 pm
by rollo19
Surely it must be possible.. it's all possible in TM1 right..?
The structure works - the only thing I struggle to do is get the Value for the relevant variable.. it's in the record, I just don't know how to pick it out..
Re: Loading many measures
Posted: Mon Mar 04, 2013 9:57 pm
by Wim Gielis
Have a look at the EXPAND function, if you can make it work, please leave me a note
But don't tell me I did not warn you you'd lose your time investigating this one. Others were there before you.
Re: Loading many measures
Posted: Mon Mar 04, 2013 10:00 pm
by David Usherwood
Re: Loading many measures
Posted: Mon Mar 04, 2013 10:15 pm
by jstrygner
I just finished replying and see David and Wim already pointed you to the same direction in the meantime, but in less detailed way, so I will let myself submit the post anyway
.
***
First of all, please note, that the codition here will always be true:
Code: Select all
IF(vElement @<> 'Expense' % vElement @<> 'Revenue');
I assume you wanted it to be:
Code: Select all
IF(vElement @<> 'Expense' & vElement @<> 'Revenue');
There is a chance you wanted it to be this:
Code: Select all
IF(vElement @= 'Expense' % vElement @= 'Revenue');
But this does not change much, both cases will work with below code.
Whatever measure names you have in your source columns, give variables for those columns meaningful names. For example for measure
Sales give a name
vValueSales, for measure
Expense make it
vValueExpense etc.
In such a case you could change your code to something like this:
Code: Select all
iMeasure = DIMSIZ('ABM Measures');
i=1;
While (i<=iMeasure);
vElement = DIMNM('ABM Measures',i);
IF(vElement @<> 'Expense' & vElement @<> 'Revenue');
vValue = Expand ( '%vValue' | vElement | '%' );
IF(CellIsUpdateable('ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement)=1);
nGetVal = CellGetN('ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement);
CellPutN(nGetVal + vValue, 'ABM', 'Actual', quarter, ctyp11, fundsor, pattyp, vWard, diag, vElement);
ENDIF;
ENDIF;
i=i+1;
END;
Above example is not tested, so you might need to change it in some way. Generally it uses a concept of very useful
indirect variables.
Re: Loading many measures
Posted: Tue Mar 05, 2013 2:26 am
by rollo19
Brilliant thanks team, this saves a lot of code. superb jstrygner, really appreciate that. I scored with: vValue = StringToNumber(Expand ( '%' | vElement | '%' ));
Re: Loading many measures
Posted: Tue Mar 05, 2013 12:21 pm
by Wim Gielis
Interesting, I stand corrected on my first post.
When time permits I will have a look at this, thanks.