Simulation of Array variable types in TI

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2057
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Simulation of Array variable types in TI

Post by Steve Rowe » Mon Oct 20, 2008 10:29 am

I often find myself wanting an array variable type in TI, this doesn't exist. As an alternative it would be good to be able to use the expand statement to substitute variable names at run time. Something like the command below which would add the parents of an element to a sequence of variables named Par1, Par2 and so on. This would avoid the use of a lengthy elseif statement.

Code: Select all

While( ixCount<=maxParent);

   Expand("Par%ixCount%")=Elpar( DimName, ElName, ixCount);
   ixCount=ixCount+1;
End;

ASCIIOutput ( FileName, ElName,Par1,Par2,Par3,Par4,Par5,Par6,Par7,Par8,Par9,Par10);
So first question is has someone already figured out how to do this? (The above doesn't compile). The closest I can come up with is to build a 2d cube and treat that as the array variable but then I still have to assign the values in the array back to the variables.

Code: Select all

(Unchecked for syntax)
While( ixCount<=maxParent);

   CellPutS(Elpar( DimName, ElName, ixCount), '2dArray','String','Counter' | Str( ixCounter,2,0));
   ixCount=ixCount+1;
End;

Par1=CellGetS('2dArray','String', 'Counter1');
Par2=CellGetS('2dArray','String', 'Counter2');
etc;
This at least avoids the use of a long If statement and is easier to debug. Has anyone thought of a better way of doing this type of thing?

Cheers,

User avatar
Steve Vincent
Site Admin
Posts: 1052
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Simulation of Array variable types in TI

Post by Steve Vincent » Mon Oct 20, 2008 2:36 pm

Actually we do use it, a TI i inherited had the same code repeated 230+ times in the data tab and i was asked to make a change which find and replace (using notepad ;) ) wouldn't have done, so i found myself thinking of a better way of writing it altogether.

My datasource is a text file but ordered in tabular format (each month for up to 19 years is a seperate column of data) and the variable names for each start at V11 and go on up to V228. As the file can cover different ranges of months, it needed to allocate each variable without needing to rewrite the TI so we use an attribute to determine witch month relates to which "column".
MthAttrb.jpg
MthAttrb.jpg (26.72 KiB) Viewed 10227 times
VarList.jpg
VarList.jpg (36.07 KiB) Viewed 10228 times
One TI is used to allocate the months to a month number, based on the users input for the 1st month in the file they are going to load. Once thats run and the view above is correct, the following code is used to load the data to the correct month.

Code: Select all

MonthN = 1;
VarN = 11;   #this is the number of the first variable that holds the first months values

While ( MonthN <= 228 ); #228 is the max i need it to check thru

MonthS = 'Month ' | NumberToString ( MonthN ); #the element i'm looking for in the attributes (month 1, month 2 etc)
VarS = EXPAND ( '%V' | NumberToString ( VarN ) | '%' ); # the key bit, grabs the value of the variable V11, V12 etc but stores it as a string


CELLPUTN ( 
                      CELLGETN ( 
                                            ' Demand ', AreaPlacingWork, NewContractNo, DeptDemandPlacedOn2, ResourceTag, Nomination, Site, 
                                            ResourceSource, ContractStatus, Risk, 
                                            ATTRS ( ' Month Number ', MonthS , ' Demand Month ' ), 
                                            Version, ' Man Hours ' 
                                           ) + NumBR ( VarS ) , #this bit converts the expanded value to a numeric so it can be added to the existing value
                      ' Demand ', AreaPlacingWork, NewContractNo, DeptDemandPlacedOn2, ResourceTag, Nomination, Site, ResourceSource, ContractStatus, 
                      Risk, 
                      ATTRS ( ' Month Number ', MonthS , ' Demand Month ' ), 
                      Version,' Man Hours ' 
                      ) ;
This isn't the full code, but its the bit the EXPAND concerns, hope it helps.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet

User avatar
Steve Rowe
Site Admin
Posts: 2057
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Simulation of Array variable types in TI

Post by Steve Rowe » Mon Oct 20, 2008 3:47 pm

That's very close but also the exact opposite of what I want to do! :roll: .

You are doing

VarS = EXPAND ( '%V' | NumberToString ( VarN ) | '%' );

but I want to use Expand on the left hand side of the statement so,

EXPAND ( '%V' | NumberToString ( VarN ) | '%' )=VarS ;

Thanks for posting though as you enabled me to get my head around what a working piece of syntax should look like. With the syntax corrected I have

Code: Select all

ixCount=1;
While( ixCount<=maxParent);
   Expand('%Par' |  NumberToString (ixCount) | '%')=Elpar( DimName, ElName, ixCount);
   ixCount=ixCount+1;
End;
This gives a compile error of "Syntax Error on or before Elpar( DimName, ElN missing semicolon"
If I change the code to

Code: Select all

ixCount=1;
While( ixCount<=maxParent);
   b=Expand('%Par' |  NumberToString (ixCount) | '%');
   a=Elpar( DimName, ElName, ixCount);
   ixCount=ixCount+1;
End;
it compiles just fine. This seems to imply that Expand cannot be used on the left hand side of a statement. What's the forums view is this a bug or was Expand not written with this use in mind? I'm going to raise as a bug report I think and see what they come back with... I'm using 8.4.5 U2

Thanks again for the input Steve

User avatar
Martin Ryan
Site Admin
Posts: 1958
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Simulation of Array variable types in TI

Post by Martin Ryan » Mon Oct 20, 2008 4:09 pm

Steve Rowe wrote: Expand('%Par' | NumberToString (ixCount) | '%')=Elpar( DimName, ElName, ixCount);
No, I don't think that's a bug, because I can't see what you're trying to achieve there. What variable are you trying to set? I think the left hand side of an equation should always be a variable that is being set.

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

User avatar
Steve Vincent
Site Admin
Posts: 1052
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Simulation of Array variable types in TI

Post by Steve Vincent » Mon Oct 20, 2008 4:38 pm

Agree with Martin, not sure why you'd want the name of a variable (left hand side) to be dynamic by using expand. Left should always be set and used later on, while its value should be the dynamic part.

Expand allows you to take the value of multiples of variables and allows its use as another, single variable. Not sure what your intended purpose is, but it looks like expand isn't the right method for it :?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet

User avatar
Steve Rowe
Site Admin
Posts: 2057
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Simulation of Array variable types in TI

Post by Steve Rowe » Mon Oct 20, 2008 7:13 pm

What I'm trying to do is avoid writing something like this

Code: Select all

If (ixCount=1);
    Par1=Elpar( DimName, ElName, ixCount);
ElseIf(ixCount=2);
    Par2=Elpar( DimName, ElName, ixCount);
Else....
.....
ElseIf(ixCount=10);
    Par10=Elpar( DimName, ElName, ixCount);
EndIf;
If we were in VBA then you would use an array and a loop to populate the varaibles like this

Code: Select all

For ixCount=1 to 10
    Par(ixCount)=Elpar( DimName, ElName, ixCount);
Next ixCount
As there is no array variable in TI then you could use a while loop and the expand function to do a similar thing

Code: Select all

ixCount=1;
While( ixCount<=maxParent);
   Expand('%Par' |  NumberToString (ixCount) | '%')=Elpar( DimName, ElName, ixCount);
   ixCount=ixCount+1;
End;
Like I said it doesn't work but it would be nice if it did.

User avatar
paulsimon
MVP
Posts: 756
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Simulation of Array variable types in TI

Post by paulsimon » Mon Oct 20, 2008 10:31 pm

Steve

I've been through the same thing when writing a process to read from any cube, where a cube may have any number of dimensions.

I found that provided you define the TI with enough variables, if you then redefine the data source to produce fewer variables, TI doesn't seem to mind. However, I could not get over the problem of doing a CellPutN, since then the number of variables separated by commas must match the number of dimensions. I thought of Expand, but as you have found, it only produces a String, not something that can be viewed as a variable by TI. The closest I got was using a series of IF ELSEIF to check the number of dimensions and call the appropriate CellPutN, but that is not particularly elegant.

You can do this using the API. I did it in my EasyAPI. However, I cannot see any way to do it in TI. What we really need is a command that lets you take a string and treat it as a variable. I think that the closest you get to this is with some of the MDX commands. However, I can't see a way to use them to enter data from TI.

Regards


Paul Simon

User avatar
Steve Vincent
Site Admin
Posts: 1052
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Simulation of Array variable types in TI

Post by Steve Vincent » Tue Oct 21, 2008 7:24 am

Indeed i see where you are coming from now. Only time i've cycled thru a variable like that is in the example i posted, and i could do that because it was in the variables tab not directly in the code. How about creating Par1 thru to Par10 in the variables tab itself and then using expand to grab the data from each one? I know its still repeating all the code but i don't see you have much option :(
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet

User avatar
Steve Rowe
Site Admin
Posts: 2057
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Simulation of Array variable types in TI

Post by Steve Rowe » Tue Oct 21, 2008 8:24 am

In the end I created a 2 d array cube with an index dimension and a string element.

Code: Select all

ixCount=1;

While( ixCount<=maxParent);
#Clear the array cube and then update the value
#Parents
   CellPutS( '' , ArrayCube, 'Count' | str(ixCount,2,0) , 'String');
   tempPar=Elpar( DimName, ElName, ixCount);
   CellPutS( tempPar, ArrayCube, 'Count' | str(ixCount,2,0) , 'String');
   ixCount=ixCount+1;
End;

Par1=CellGetS( ArrayCube, 'Count1' , 'String');
Par2=CellGetS( ArrayCube, 'Count2' , 'String');
Par3=CellGetS( ArrayCube, 'Count3' , 'String');
Par4=CellGetS( ArrayCube, 'Count4' , 'String');
Par5=CellGetS( ArrayCube, 'Count5' , 'String');
Par6=CellGetS( ArrayCube, 'Count6' , 'String');
Par7=CellGetS( ArrayCube, 'Count7' , 'String');
Par8=CellGetS( ArrayCube, 'Count8' , 'String');
Par9=CellGetS( ArrayCube, 'Count9' , 'String');
Par10=CellGetS( ArrayCube, 'Count10' , 'String');

ASCIIOutput ( blah )

It's still more wordy than I would like but gets rid of the nasty Else If statement. I think this makes the TI easier to read, structurally simpler and more extendable. If I wanted to check for 100 parents say then I can change the value of the maxParent constant and add more ParX=... statements (easy to construct in Excel) but I don't have to mess around with a messy ElseIf statement which has a fiddly syntax.

What I don't like is that the TI now needs a cube to run correctly which means when I move from live to dev I need to move the cube too. I guess the next step would be to write a TI that builds the array cube for me, so that I can create and delete the array cube "in-process". I'm going to save that for a rainy day though!

Cheers,

User avatar
garry cook
MVP
Posts: 198
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: Simulation of Array variable types in TI

Post by garry cook » Fri Jan 25, 2013 10:59 am

Yes, I realise this is thread necormancy however I've found myself today coding a TI to have dynamic variable generation using EXPAND on left hand side of a formula as per Steve's comment above.

I would swear blind that I've done this previously. In fact, I'd swear blind I'd done it for the exact same functionality that I'm putting in this TI (multi-nested while loops with variable names based on ELLEV) but it's failing to compile as noted on this thread. I've used it on the right hand side of the formulae plenty of times but I'm sure I've managed to get it to work on the left also.

It's possible that I'm imagining this I guess but since this thread is over 4 years old now, thought it worth checking as I'm convinced I've done this before and can't find any more recent posts on the subject. Has anyone found a syntaxical workaround to the left hand sided dynamic variable generation using EXPAND that doesn't involve building a seperate array cube?

Not too bothered about it for this TI as it's just streamlining but it's bugging me now.

Hoping I've not gone mad here :?
TIA

lotsaram
MVP
Posts: 3413
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Simulation of Array variable types in TI

Post by lotsaram » Mon Jan 28, 2013 9:00 am

This is what I would call thread necromancy.
(Good thing I watched series 3 of TB, otherwise I confess would have no idea what necromancy was ;) )

User avatar
Harvey
Community Contributor
Posts: 218
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Simulation of Array variable types in TI

Post by Harvey » Fri Feb 01, 2013 8:17 am

I can't believe this thread has been around for over 4 years! When it cropped up again, it reminded me I had intended to write a blog post about using character-delimited strings to simulate array variables in Turbo Integrator.

So I finally got around to it: here is the article (with helper/library code at the bottom of the article)
Flow OLAP - thinking outside the cube.

mnasra
Posts: 104
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Simulation of Array variable types in TI

Post by mnasra » Wed Jan 14, 2015 7:19 pm

yes, it is 2015- Yes, I have the same problem. I tried to open the document mentioned above, but it is not there anymore.
Do anyone knows in 2015 how to created array variables in TI and give these variables a value.

In other terms I want to say (in as few lines as possible).
var(1) = Index
Var(2) = Index +1
Var(3) = Index +2
..... Var(100) = index + 99
Thanks
Micheline

User avatar
Steve Rowe
Site Admin
Posts: 2057
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Simulation of Array variable types in TI

Post by Steve Rowe » Thu Jan 15, 2015 6:18 pm

The rest of the thread should give you a pretty good idea of what you need to do.

If it is the left hand side of the statement that needs to use an array there is no way to use an array variable in TI, however a 2d cube is exactly like an array variable (though I guess it is slower to address). So write some code to create a 2d cube on the fly and use this, note though that this can't be done if you want the TIs to execute in parallel (I assume).

If it is the right hand side of the statement then you need to use the expand function.
HTH

declanr
MVP
Posts: 1663
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: Simulation of Array variable types in TI

Post by declanr » Thu Jan 15, 2015 7:05 pm

Or if you really want to read the thread where the link is broken; click on it anyway thrn from the error page use the link that directs you to the homepage/blog and scroll down to February 2013 (the date of the post above) and hey presto....
Declan Rodger

mnasra
Posts: 104
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Simulation of Array variable types in TI

Post by mnasra » Thu Jan 15, 2015 11:23 pm

Thank you very much. It was the left hand side. I will create a cube for that.
;) ;)
Thanks
Micheline

User avatar
Harvey
Community Contributor
Posts: 218
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Simulation of Array variable types in TI

Post by Harvey » Sat Jan 17, 2015 12:13 pm

Or this: http://blog.flowolap.com/post/2013/02/0 ... rator.aspx

Sorry, not sure how the link got broken...
Flow OLAP - thinking outside the cube.

User avatar
Harvey
Community Contributor
Posts: 218
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Simulation of Array variable types in TI

Post by Harvey » Tue Nov 03, 2015 2:53 am

Flow OLAP - thinking outside the cube.

Wim Gielis
MVP
Posts: 2472
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Simulation of Array variable types in TI

Post by Wim Gielis » Tue Nov 03, 2015 8:57 am

Hello Harvey,

The drawback here is that a big number of processes are called.
Each time filling up the TM1 server message log, but also maybe leading to problems when processes call other processes and so on.

Would Java Extensions be an improvement ? Like ExecuteJavaN and ExecuteJavaS ?

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Post Reply