Concatenated string with Expand Function
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Concatenated string with Expand Function
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.
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.
-
- MVP
- Posts: 3119
- 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: Concatenated string with Expand Function
Hello,
You can use:
Another topic that uses Expand:
http://www.tm1forum.com/viewtopic.php?f ... and#p65616
Wim
You can use:
Code: Select all
vcount=1;
While(vcount<=12);
SubsetElementInsert(vDimName, vSubsetName, Expand('%vrollinggroup'|NumberToString(vcount) |'%'), vcount);
Vcount=vcount+1;
END;
http://www.tm1forum.com/viewtopic.php?f ... and#p65616
Wim
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: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Concatenated string with Expand Function
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.
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.
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Concatenated string with Expand Function
Wim's suggested code should work well.
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.
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 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.
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".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 .
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.
Declan Rodger
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: Concatenated string with 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
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:
Code: Select all
vrollinggroup1='Jan 2017';
vrollinggroup2='Feb 2017';
vrollinggroup3='Mar 2017';
Upto 12 at the top of the prologue tab and before any of your code and try it again for us?
Jules
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Concatenated string with Expand Function
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.
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: Concatenated string with Expand Function
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 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
- 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: Concatenated string with Expand Function
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...
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...
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Concatenated string with Expand Function
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.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...
- gtonkin
- MVP
- Posts: 1200
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Concatenated string with Expand Function
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...
Guessing that 2018 will need some work with current approach...
-
- MVP
- Posts: 3119
- 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: Concatenated string with Expand Function
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.
I would go for:
Code: Select all
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup1, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup2, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup3, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup4, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup5, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup6, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup7, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup8, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup9, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup10, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup11, 0 );
SubsetElementInsert( vDimName, vSubsetName, vrollinggroup12, 0 );
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.
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
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Concatenated string with Expand Function
What wizardry is this?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.
You mean to say that all these years of incrementing a counter for adding an element to the end of a subset are wasted!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 3119
- 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: Concatenated string with Expand Function
Exactly ! Those moments are lost and will never come back !
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
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
SubsetElementInsert with index of 0
I can't believe I only just found this out. This goes straight into the playbook.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 3119
- 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: SubsetElementInsert with index of 0
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
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
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Concatenated string with Expand Function
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.
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: SubsetElementInsert with index of 0
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!
Cheers Wim, I didn't know that either!