Loading many measures

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

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

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

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

[David Usherwood]

http://www.tm1forum.com/viewtopic.php?f=21&t=4921
Looks like this is what you want.

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

[rollo19]

Brilliant thanks team, this saves a lot of code. superb jstrygner, really appreciate that. I scored with: vValue = StringToNumber(Expand ( '%' | vElement | '%' ));

[Wim Gielis]

Interesting, I stand corrected on my first post.
When time permits I will have a look at this, thanks.