TI Process: WHILE loop referencing passed parameters

Post Reply
Extirpator
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

TI Process: WHILE loop referencing passed parameters

Post by Extirpator » Thu Sep 05, 2019 10:25 pm

I am trying to provide users with the ability to add up to 20 elements (employees) to a particular cost centre. I am passing the parameters from Excel but I have also run the process manually and filled in a test element name and test caption and the result is the same.

I am attempting to use a WHILE loop in the TI process to go through the parameters and if they're not empty, then add the elements to the dimension and populate the caption.

If I write it like this, the PAW TI editor recognizes pAdd1 as a parameter being provided and adds the desired element:
DimensionElementInsertDirect(DimName, '', pAdd1, 'N');

However, when I put it in the WHILE loop and attempt to concatenate the number to the end of pAdd to get the equivalent of pAdd1, it does what I would expect (but not what I want) and literally adds the element pAdd1 to my dimension... rather than picking up the pAdd1 parameter.
DimensionElementInsertDirect(DimName, '', 'pAdd'|NumberToString(Counter), 'N');

So the question is whether it is possible to use the counter to add the elements specified by pAdd1, pAdd2, pAdd3, ..., pAdd20?

The only option I've come up with is to start with 20 lines (and 20 more for the caption) saying if Counter = 1, pAddMe = pAdd1 and if Counter = 2, pAddMe = pAdd2, etc. and then the information inside the WHILE loop would just deal with pAddMe. It is a bit sloppy to have 40 lines (20 for the elements and 20 for the parameters) just to do what I'm trying to do with concatenating the counter to pAdd.

I know it is doing exactly what I'm asking but I can't figure out how to achieve what I'm trying to do. I've done my sloppy, non-scalable workaround for years and I'm sure there is a better way.

Code: Select all

#Section Prolog
DimName = 'siteEmployees';
MaxCounter = 20;

if (pExecute=0);
	ProcessQuit;
endif;

#Section Metadata
DimensionSortOrder(DimName, 'ByInput','Ascending', 'ByName', 'Ascending');

Counter = 1;

WHILE(Counter <= MaxCounter);
	IF('pAdd'|NumberToString(Counter) @<> '');
		DimensionElementInsertDirect(DimName, '', 'pAdd'|NumberToString(Counter), 'N');
    	DimensionElementComponentAddDirect(DimName, pCostCentre, 'pAdd'|NumberToString(Counter), 1);
    ENDIF;
    Counter = Counter + 1;
END;

DimensionUpdateDirect(DimName);

#Section Data
# If I can get the above working then I'll repeat the WHILE loop here to populate the caption.
#  AttrPutS('pCaption'|NumberToString(Counter), DimName, 'pAdd'|NumberToString(Counter), 'Caption');

#Section Epilog
SecurityRefresh;
Note that I do use indentations but this editor (or my use of it) is removing all of my indents.

Admin Note: Please use the Code tag to surround code blocks for readability.

Emixam
Posts: 36
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: TI Process: WHILE loop referencing passed parameters

Post by Emixam » Thu Sep 05, 2019 10:45 pm

Hi,

You should use the Expand function.

I didn't really read your code but here's what your WHILE loop should looks like:

Code: Select all

WHILE( Counter <= MaxCounter );
	sCurrentParam = Expand( '%' | 'pAdd' | NumberToString( Counter ) | '%' );
	IF( sCurrentParam @<> '');
		DimensionElementInsertDirect( DimName, '', sCurrentParam, 'N' );
    	        DimensionElementComponentAddDirect( DimName, pCostCentre, sCurrentParam, 1 );
        ENDIF;
        Counter = Counter + 1;
END;
Let me know if it works !
Last edited by Emixam on Thu Sep 05, 2019 11:11 pm, edited 2 times in total.

Extirpator
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

Re: TI Process: WHILE loop referencing passed parameters

Post by Extirpator » Thu Sep 05, 2019 10:54 pm

Awesome! You're my hero of the week.

Do you prefer STR over NumberToString? Fewer parameters and no need to TRIM, I would think.

Many thanks,
Benjamin

Emixam
Posts: 36
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: TI Process: WHILE loop referencing passed parameters

Post by Emixam » Thu Sep 05, 2019 10:58 pm

Extirpator wrote:
Thu Sep 05, 2019 10:54 pm
Do you prefer STR over NumberToString? Fewer parameters and no need to TRIM, I would think.
** I update my code.. It was an old code with STR and TRIM **
Extirpator wrote:
Thu Sep 05, 2019 10:54 pm
Awesome! You're my hero of the week.
"No, don't call me a hero. Do you know who the real heroes are? The guys who wake up every morning and go into their normal jobs, and get a distress call from the Commissioner and take off their glasses and change into capes and fly around fighting crime. Those are the real heroes."
Last edited by Emixam on Thu Sep 05, 2019 11:16 pm, edited 4 times in total.

Extirpator
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

Re: TI Process: WHILE loop referencing passed parameters

Post by Extirpator » Thu Sep 05, 2019 11:06 pm

The only other thing I did was to change '%' | 'pAdd' to '%pAdd' since it was concatenating two strings directly next to each other. Though honestly I liked the symmetry of yours so I might go back just so it looks prettier.

Final version:

Code: Select all

sCurrentParam = Expand('%pAdd' | NumberToString (Counter) | '%');
Works like a charm. So happy!

Emixam
Posts: 36
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: TI Process: WHILE loop referencing passed parameters

Post by Emixam » Thu Sep 05, 2019 11:31 pm

Extirpator wrote:
Thu Sep 05, 2019 11:06 pm
The only other thing I did was to change '%' | 'pAdd' to '%pAdd' since it was concatenating two strings directly next to each other.
Usually I'm using a variable instead of a string. It's less confusing and easily reusable.

Code: Select all

sSubString = 'Whatever';
sCurrentWhatever = Expand( '%' | sSubString | NumberToString( Counter ) | '%' );
Cheers !

Extirpator
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

Re: TI Process: WHILE loop referencing passed parameters

Post by Extirpator » Fri Sep 06, 2019 12:27 am

I agree and changed it back. Easier to understand and pleases the symmetry part of my brain.

PS... Fully agree and appreciate your hero statement.

Post Reply