Expand function with numbers

Post Reply
Wim Gielis
MVP
Posts: 3105
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:

Expand function with numbers

Post by Wim Gielis »

Hi all,

I am trying to log TI process run times in such a way, that I only need to specify some code at the top of the Prolog tab,
followed by very minimal code further on in the process. If we can get this working, generic processes like Bedrock will be able to benefit too.

The cases is as follows. I have a master process that can complete a number of tasks. These tasks are numbered 0, 1, 2, ..., 9. The user executes the mother process by specifying a string parameter: pMode = '1489' which means: execute subparts 1, then 4, then 8, then 9. The process uses recursion, hence, the same process calls itself but then pMode = 1, then pMode = 4, then pMode = 8, then pMode = 9.

For each of the 10 subparts, I want to track the runtime, so I need to log (with LogOutput) the START, the END, and calculate the difference in seconds. This is not that difficult, in fact, this works fine (all Prolog tab code):

Code: Select all

cINFO_Mask_Process_Start = GetProcessName | ' START';
cINFO_Mask_Process_End = GetProcessName | ' END ( after %sSec% sec.)';
cMask_Sec = '#,##0.00';


# start logging
nStart = Now;
LogOutput( 'INFO', Expand( '%cINFO_Mask_Process_Start%' ));

# some code

# wait 2 seconds
Sleep( 2000 );

# some more code

# calculate the time
sSec = NumberToStringEx( 86400 * ( Now - nStart ), cMask_Sec , '.', ',' );
LogOutput( 'INFO', Expand( Expand( '%cINFO_Mask_Process_End%' )));
I just want to use a mask for the start, a mask for the end, and that's it. nStart = Now must be used, we can't do without that line.
But I hope to get rid of the line sSec = ..... (the last but one line). I prefer to "calculate"/define sSec at the top of the Prolog tab, containing the variable nStart, with is populated when the subprocess takes off.

With the use of Expand, I had hoped to get this working. But it does not. Here's the code, which you can just copy/paste to replicate:

Code: Select all

cINFO_Mask_Process_Start = GetProcessName | ' START';
cINFO_Mask_Process_End = GetProcessName | ' END ( after %sSec% sec.)';
nStart = 0;
sSec = NumberToStringEx( 86400 * ( Now - nStart ), '#,##0.00' , '.', ',' );


# start logging
nStart = Now;
LogOutput( 'INFO', Expand( '%cINFO_Mask_Process_Start%' ));

# some code

# wait 2 seconds
Sleep( 2000 );

# some more code

# calculate the time
LogOutput( 'INFO', Expand( Expand( '%cINFO_Mask_Process_End%' )));
The output in the server log is:
1480 [4] INFO 2020-07-10 20:06:46.958 TM1.TILogOutput TI_test START
1480 [4] INFO 2020-07-10 20:06:48.958 TM1.TILogOutput TI_test END ( after 1,910,030,806.00 sec.)
That's a lot of seconds ! :lol: Clearly it is caused by nStart = 0 and sSec is not recalculated anymore. However, I use a bunch of Expand's in the END mask.

It's more out of curiosity but working with Expand and masks can really increase the maintenance of code. Is there anyone who can make it work with the sSec = ... line at the top of the Prolog, where all the information is neatly organised - except the parts that we cannot know upfront ?
Thanks heaps !
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
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Expand function with numbers

Post by ascheevel »

It would be nice if you could define callable functions in TI script. You could set up a separate TI for this function, but I'm sure you're already thought of that and perhaps you don't want the additional process start/finishes muddying up the log entries you're trying to make.

It's not quite what you're looking for, but the below doesn't use a sSec variable and instead embeds the NumberToStringEX in the LogOutput string. The LogOutput can of course be condensed to one line but I broke it out for the example.

Code: Select all

cINFO_Mask_Process_Start = GetProcessName | ' START';
cINFO_Mask_Process_End1 = GetProcessName | ' END ( after ';
cINFO_Mask_Process_End2 = ' sec.)';

# start logging
nStart = Now;
LogOutput( 'INFO', Expand( '%cINFO_Mask_Process_Start%' ));

# some code

# wait 2 seconds
Sleep( 2000 );

# some more code

# calculate the time
LogOutput('INFO'
	, cINFO_Mask_Process_End1 
	| NumberToStringEx( 86400 * ( Now - nStart ), '#,##0.00' , '.', ',') 
	| cINFO_Mask_PRocess_End2
	);
Wim Gielis
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Thanks ascheevel. If we can only use 'masks' in the last LogOutput line, that would be great :-) Tricky problem I'm afraid.
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: Expand function with numbers

Post by lotsaram »

Could it be that Expand rounds numbers to 4 decimal places? ... and therefore your “day part” goes to zero?

You could get around this by multiplying by a factor of 10^6 and then dividing again.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Interesting, I will play around to see if I can get it working with as minimal code in the LogOutput as necessary. If that means more code (once) in the Prolog tab I’m fine 🙂
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
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

I don't think I can get it working the way I would like to see it in practice :)

This code:

Code: Select all

nStart = Now;
s = NumberToString( Mod( nStart, 1 ) * 10^6 );
LogOutput( 'INFO', Expand( '%s%' ));

Sleep( 5000 );

nStart = Now;
LogOutput( 'INFO', Expand( '%s%' ));
returns:
21776 [a] INFO 2020-07-11 12:15:51.019 TM1.TILogOutput 511006,944445398
21776 [a] INFO 2020-07-11 12:15:56.019 TM1.TILogOutput 511006,944445398
Hence, the string variable "s" is not recalculated a second time, even though I explicitly assign the current time of Now to nStart right before calling it in the last line.
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
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Lotsaram is right, the problem is in the 3 decimals when Expand is applied to a numeric value.

The challenge is to enter '...' and %...% around the variable n in line 2.
But if we do that, the MOD function will complain.

Code: Select all

n = 0;
s = NumberToString( Mod( n, 1 ) * 10^6 );


n = Now;
LogOutput( 'INFO', Expand( s ));

Sleep( 2000 );

n = Now;
LogOutput( 'INFO', Expand( s ));
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
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Expand function with numbers

Post by ascheevel »

Wim Gielis wrote: Sat Jul 11, 2020 10:18 am Hence, the string variable "s" is not recalculated a second time, even though I explicitly assign the current time of Now to nStart right before calling it in the last line.
Isn't this expected behavior of the EXPAND function? My understanding of EXPAND is that it can only return the value computed at runtime and is happily ignorant of any logic employed on the right side of the equals sign to derive that value. In your example, you only have one formula to determine the value of S, but if you had multiple statements in your code for the value of S and EXPAND was to do more than simply return the last computed value it would need to know which formula to apply. The last formula for S would be an obvious choice, but not something I believe EXPAND is capable of. I'm not saying it wouldn't be nice, it would defacto give us the ability to define a callable function within TI script. Below is an example illustrating what I'm trying to type.

Code: Select all

n = 2;

n1 = n * 2;
# n1 =  4
n1 = n * 3;
# n1 = 6
n1 = n * 4;
# n1 = 8

# last computed value of n1 is 8, any use of EXPAND('%n1%') should return 8, no matter how the value of n is changed

n = 3;
LogOutput('INFO', EXPAND('%n1%'));
# will return 8 and not 12 because 8 is the last value computed for n1: (2*4)
Wim Gielis
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Hi ascheevel,

I guess you have a different interpretation of my case. Your n1 definition should be fixed. It's only that it should take different values for n, each time you call it through Expand. I agree that if the definition of n1 changes, only the last definition can and should be used.

In fact, this works:

Code: Select all

s = '%n%';

n = 2;
LogOutput( 'INFO', Expand( s ));

n = 4;
LogOutput( 'INFO', Expand( s ));
It gives you 2, then 4.

Imagine now that n is equal to the value of 'Now' (when called).
And variable 's' contains a calculation/text for LogOutput, making use of 'n'. Should be working, shouldn't it ?
And in fact we can, but chopping off Now at 3 decimals after Expand is killing the detail of seconds and therefore, my LogOutput is giving nonsense.

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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Expand function with numbers

Post by Mark RMBC »

Hi Wim,

I feel like you have pulled the 5 card trick here, in your first comment you
reference nStart but never expand nStart but in your latest example you expand N both times.

So the issue with your original problem has nothing to do with decimal places and expand does it?
The problem is you cant expand nStart and it always ends up being zero?

I don't see a way out of that problem.

Apologies if I have misunderstood all this ;)

regards,

Mark
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Expand function with numbers

Post by ascheevel »

I shouldn't have posted multiple definitions of n1 in my example. I see now it's an unnecessary distraction.

Your example makes sense and performs as I would expect, but not because there's anything special about the variable s. EXPAND(s) in your example is the exact same as writing EXPAND('%n%'). In fact at runtime, that's what the line of script will be executed as, s will be switched to '%n%' at execution. Variable s in your example just serves as the string argument for the EXPAND function: the variable n with the required parentheses for EXPAND to work. The reason two different values are outputted is because you explicitly changed the value of n before calling the second EXPAND on n via s.

To expand on your simplified example: if instead of having s reference '%n%', it references a new variable '%n1%' that is the difference between n and 1, we can see that n1 is never reevaluated.

Code: Select all

n = 2;
s = '%n1%';
n1 = n - 1;

LogOoutput('INFO', EXPAND(s));
# will output 1

n = 3;
LogOutput('INFO', EXPAND('s'));
# will still output 1 because n1 is not reevaluated by calling EXPAND on s

Putting an EXPAND in n1 will have no effect

Code: Select all

n = 2;
s = '%n1%';
n1 = NUMBR(EXPAND('%n%')) - 1;

LogOoutput('INFO', EXPAND(s));
# will output 1

n = 3;
LogOutput('INFO', EXPAND('s'));
# will still output 1 because n1 is not reevaluated by calling EXPAND on s

I think this goes back to my opinion that you're asking EXPAND to do what it cannot: recalculate the value of a variable. I definitely could still be missing something though, wouldn't be the first time.
Wim Gielis
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Well, in your code (where I corrected some typos):

Code: Select all

n = 2;
s = '%n1%';
n1 = NUMBR(EXPAND('%n%')) - 1;

LogOutput('INFO', EXPAND(s));
# will output 1

n = 3;
LogOutput('INFO', EXPAND(s));
# will still output 1 because n1 is not reevaluated by calling EXPAND on s
you introduce the variable n1. Why not leave out n and define s with some sort of calculation/definition - that needs to be recalculated with differing input values. But leaving out other intermediate variables like n1. Basically, as this works:

Code: Select all

s = '%n%';

n = 2;
LogOutput( 'INFO', Expand( s ));

n = 4;
LogOutput( 'INFO', Expand( s ));
and gives:

Code: Select all

22820   [4]   INFO   2020-07-13 23:16:47.983   TM1.TILogOutput        2.000
22820   [4]   INFO   2020-07-13 23:16:47.983   TM1.TILogOutput        4.000
we should 'only' need to make s more useful and we have it. BUT... Expand on numbers stops with 3 decimals. Therefore, this:

Code: Select all

s = '%n%';

n = 0.0002;
LogOutput( 'INFO', Expand( s ));

n = 0.0004;
LogOutput( 'INFO', Expand( s ));
yields the same output:
22820 [4] INFO 2020-07-13 23:18:01.136 TM1.TILogOutput 0.000
22820 [4] INFO 2020-07-13 23:18:01.136 TM1.TILogOutput 0.000
That's the real issue (as also pointed out by Lotsaram). This being said, I don't now how to overcome this limit on decimals and be able to convert it into a (string) number of seconds elapsed.
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
HighKeys
Posts: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: Expand function with numbers

Post by HighKeys »

Hi,

Its a lil bit advanced here, but may i find the tree in the forest :lol:

Why you dont just do it like that:

Code: Select all

nStart = NOW();

Sleep(2000); #your code here...

nRunTime = FormatDate( NOW() - nStart, 'ss', 0 ) ;

ASCIIOutput('C:\Datetime.txt',  nRunTime );
nRunTime Output is 02. So you could calulate Minutes, hours from that.

But i think you already have done this and there is a reason for using expand?

At the moment i track the runtime of my TI like above.

BR
Wim Gielis
MVP
Posts: 3105
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: Expand function with numbers

Post by Wim Gielis »

Hello,

That FormatDate function is a good idea but will not solve my problem :)

The use case is that I have a process that executed itself through recursion and changing parameter values 10 times. Let’s call it 10 subparts all programmed in the same TI. Now I need to track how long each subpart runs. 1 LogOutput message when subpart X starts and another LogOutput message when subpart X ends with it’s run time. This means 20 LogOutput messages scattered around in the TI process. If I borrow your variable names, I need to define nStart and nRuntime too. This means 40 lines of code.
Why not reducing some of this by defining the calculation for nRunTime near the top of the Prolog tab (once) and using it 10 times in the ending LogOutput call - for example with Expand that takes timings at a certain point in time and injects them (10 times) in the general definition. This saves us from editing the TI process at multiple places and increases the usability.

More in general, in the absence of callable functions, could we use this kind of definition instead ?
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
Post Reply