Concatenated string with Expand Function

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Concatenated string with Expand Function

Post by Analytics123 »

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.
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

Hello,

You can use:

Code: Select all

vcount=1;
While(vcount<=12);
SubsetElementInsert(vDimName, vSubsetName, Expand('%vrollinggroup'|NumberToString(vcount) |'%'), vcount);
Vcount=vcount+1;
END;
Another topic that uses Expand:
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
Analytics123
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

Post by Analytics123 »

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

Post by declanr »

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.
Declan Rodger
User avatar
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

Post by JulianS »

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:

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
tomok
MVP
Posts: 2831
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

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
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

Post by JulianS »

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
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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...
Technical Director
www.infocat.co.uk
tomok
MVP
Posts: 2831
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

Post by tomok »

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 :oops: ), then there is no way in heck I'm going to waste my time with it.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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...
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

Hi

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 );
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.
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
lotsaram
MVP
Posts: 3651
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

Post by lotsaram »

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!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

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 !
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
lotsaram
MVP
Posts: 3651
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

Post by lotsaram »

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.
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

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
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
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Concatenated string with Expand Function

Post by David Usherwood »

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.
User avatar
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

Post by JulianS »

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! :lol:
Post Reply