TM1 Forum

Hi ,

I am having an issue in the below code .

I have 12 variables defined in my TI which has month values .

vrollinggroup1=Jan 2017;
vrollinggroup2=Feb 2017;
vrollinggroup3=Mar 2017;

Now I have to create a subset which adds all these 12 elements , so i am doing a while loop

vcount=1;
While(vcount<=12);
SubsetElementInsert(vDimName, vSubsetName, 'vrollinggroup'|NumberToString(vcount), vcount);
Vcount=vcount+1;
END;

But this doesnt work as the string variable is not considered as an element and rather a string .

Now to fix this I know i have to use expand function , but i am not sure how exactly it should be written , I read the manual and it says expand("%v1")
but that throws syntax error for me.

Any help is appreciated.

Hello,

You can use:
Another topic that uses Expand:
http://www.tm1forum.com/viewtopic.php?f ... and#p65616

Wim

I used the code ,no error on saving the TI , but when I run the process I get an error,

the element vrollingroup1 Dimension element not found.

But instead of using the while loop I directly add vrollinggroup1 to subsetelementinsert at position 1 , then it inserts the element .

Any suggestions if there is any different approach other than adding 12 lines of code to insert.

Wim's suggested code should work well.

Analytics123 wrote: ↑Fri Jul 21, 2017 8:51 pm I used the code ,no error on saving the TI , but when I run the process I get an error,

the element vrollingroup1 Dimension element not found.

What is the exact error you received? It may be a typo in the forum rather than the code but your variable suggests it is "RollingGroup" with 2 "G"'s but the error you mention only has 1 "G".

Analytics123 wrote: ↑Fri Jul 21, 2017 8:51 pm But instead of using the while loop I directly add vrollinggroup1 to subsetelementinsert at position 1 , then it inserts the element .

When you did this what was the exact code you used? Did you use the variable "vRollingGroup1" or did you use a string of "Jan 2017".

Have you created your variables in the TI tabs (prolog etc.) or are they specified as manually created variables on the variable tab? If its the latter then they won't be available to use in the prolog and will show as empty strings.

I would suggest to debug it by removing specific formulas and instead use an asciioutput in the while loop to put the results to a text file; then you can see the exact results of the expand function.

Hi all,

Yep that code looks fine to me too.

As Declan says, I think it is a typo eg missing 'g', or your variable is declared in the 'Variables', which are not visible to the Prologue tab

Can you manually put:


Upto 12 at the top of the prologue tab and before any of your code and try it again for us?

Jules

What an unbelievable waste of time. Since you're hard coding the fact there are always going to be 12 variables I would just put 12 SubSetElementInserts in the code and be done with it. The only thing I would add would be to wrap each one inside an IF statement that doesn't insert if the variable value is empty.

Hi Tom,

I agree with you, but maybe Analytics123 is using this for this is as a starting point for a far more complicated system, e.g. some kind of rolling period.

We just don't know, as the its only a query regarding EXPAND function.

I just think that maybe he should be given the benefit of the doubt.

Kind Regards

Jules

I think that even in this simple case the expand approach is well worth the (should be small) additional effort of getting it working.

The repeated part of the code is reduced to the variable assignment and this can often be constructed very simply in Excel.
The syntactically complex part of the code is written once and looped over, rather than 12 times, minimising the risk of error.
Maintainability is improved.
Speed of code writing.

This ignores some side benefits too, like.
Developer learns the use of Expand and is able to identify other places it can be used.
While loop experience.
Portability of code.

So I guess one persons unbelievable can be another's very sensible...

Steve Rowe wrote: ↑Mon Jul 24, 2017 10:53 am I think that even in this simple case the expand approach is well worth the (should be small) additional effort of getting it working.

The repeated part of the code is reduced to the variable assignment and this can often be constructed very simply in Excel.
The syntactically complex part of the code is written once and looped over, rather than 12 times, minimising the risk of error.
Maintainability is improved.
Speed of code writing.

This ignores some side benefits too, like.
Developer learns the use of Expand and is able to identify other places it can be used.
While loop experience.
Portability of code.

So I guess one persons unbelievable can be another's very sensible...

Being a consultant, I always look at issues like this with the lens of spending my time the most wisely. If the enhanced coding, like using the EXPAND function here, is going to save me or the client time in the long run then I go for it. If I see a long payback, or the alternative is really simple, like 12 lines of code instead of a while loop (which by the way has a hard-coded loop of 12 in it ), then there is no way in heck I'm going to waste my time with it.

For my 2 cents, assuming we knew the bigger picture, beyond adding 12 elements to a subset, I would have rather gone with a parameter for year and enumerated a subset/dim with Months and concatenate per the variables required. Per Tomok, being a consultant, you try build a solution that is valuable and future-proof as possible within budget etc. etc.
Guessing that 2018 will need some work with current approach...

Hi

I would go for:
In Notepad++ this is quickly done. TM1 administrators that inherit this code will easily understand it, Expand is not so obvious.
I certainly understand the advantages of the Expand function but in this case I would say: K.I.S.S.

I recently used Expand to load 125 measures from an ODBC table. I added the field names as an alias to the measures dimension. I looped over the measures with a simple While loop. Then I retrieve the alias value and used Expand to load the value against it.

Note the use of 0 as the index in the SubsetElementInsert function. As 0 logically comes before 1, an index of 0 means: add it to the end of the subset.

Wim Gielis wrote: ↑Mon Jul 24, 2017 9:39 pm Note the use of 0 as the index in the SubsetElementInsert function. As 0 logically comes before 1, an index of 0 means: add it to the end of the subset.

What wizardry is this?
You mean to say that all these years of incrementing a counter for adding an element to the end of a subset are wasted!

lotsaram wrote: ↑Tue Jul 25, 2017 4:28 amWhat wizardry is this?
You mean to say that all these years of incrementing a counter for adding an element to the end of a subset are wasted!

Exactly ! Those moments are lost and will never come back !

I can't believe I only just found this out. This goes straight into the playbook.

lotsaram wrote: ↑Tue Jul 25, 2017 6:58 am I can't believe I only just found this out. This goes straight into the playbook.

You're welcome.

This one is probably as classic as the one on selecting variable contents in TI: http://www.tm1forum.com/viewtopic.php?f ... 724#p41724

If you can write or specify your own SQL I find UNPIVOT (https://technet.microsoft.com/en-us/lib ... .105).aspx) to be a handy way to unpack lots of data columns into rows to save CELLPUTNs.

I can't believe I only just found this out. This goes straight into the playbook. +1 from me too

Cheers Wim, I didn't know that either!