IF function return undefined type Error

Post Reply
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

IF function return undefined type Error

Post by Drg »

Hey brains.


how you workaround this issue:

Code: Select all

Example1:
proc = pProc;
executeprocess( 
  proc , 
  IF(param_type @= 'N' , Numbr( param_val ) , param_val )
);

Example2:
if( param_type @= 'N' );
out = Numbr( param_val );
ELSE;
out = param_val;
ENDIF;

OR

out = IF(1=1 , '1' , 1);
so far a solution with multiple IF statements :ugeek:
Last edited by Drg on Mon Oct 28, 2019 10:01 am, edited 2 times in total.
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: IF function return undefined type Error

Post by tomok »

How about using the correct comparison operator for strings? @= instead of =. ;)
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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: IF function return undefined type Error

Post by Mark RMBC »

It would also help if example 1 had the correct number of parenthesis!
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: IF function return undefined type Error

Post by Drg »

Thanks guys.
I repair post.
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: IF function return undefined type Error

Post by Wim Gielis »

Drg wrote: Mon Oct 28, 2019 10:02 am Thanks guys.
I repair post.
Can you also repair your post such that it becomes clear what you want to achieve ? What issues you are encountering ?
What are variables / parameters / what is the data source ?
Your exact code (where all typo's are corrected)

And so on. Maybe if you do so, helpers can actually help you.
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
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: IF function return undefined type Error

Post by Drg »

Wim Gielis wrote: Mon Oct 28, 2019 10:24 am
Drg wrote: Mon Oct 28, 2019 10:02 am Thanks guys.
I repair post.
Can you also repair your post such that it becomes clear what you want to achieve ? What issues you are encountering ?
What are variables / parameters / what is the data source ?
Your exact code (where all typo's are corrected)

And so on. Maybe if you do so, helpers can actually help you.

The question is more philosophical.
What options do you (forum users) see for solving the problem of an indefinite data type (no matter where from the database or from a text file).
Let me explain to you that a string data type always comes to you, but a string may contain a number (a good example would be a file with an unknown set of analytics).
And actually the question itself sounds what options do you use?

A few examples from life:
starting a process (each time different) with parameters (each time different for a particular process)
Processing (not loading, in loading we can rely on the data type of cells in the cube) of the file (with a dynamic set of analytics)

I’m sure I’m not the only one who faced the problem when you need to put a string or number in a variable (or in a parameter of a TI function).

And I just wanted to find out who and how to solve it and talk about it
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: IF function return undefined type Error

Post by lotsaram »

Drg wrote: Wed Oct 30, 2019 1:46 pm The question is more philosophical.
What options do you (forum users) see for solving the problem of an indefinite data type (no matter where from the database or from a text file).
Let me explain to you that a string data type always comes to you, but a string may contain a number (a good example would be a file with an unknown set of analytics).
And actually the question itself sounds what options do you use?
Now you are asking a really interesting question!
In TM1 it is a REAL PAIN that
1. variable type once declared is forever immutable
2. we ony have numeric and string types

The 1st is not normally a pain but becomes very much a real pain when writing generic processes where you don't know the data type of each field or variable in advance and also for generic wrapper proceses that might call other processes and need to know the parameter types of processes they are calling. It leads to one hell of a lot of complication and unecessary duplication.

But to limit the discussion to processes dealing with variables of unknown type. There really is only one viable approach, set all unknown variables to string then on the data tab you need to examine the contents of the variable to determine if it contains any non numeric characters (plus decimal separator and minus sign), typically with a Scan function
e.g.

Code: Select all

i = 1;
nErr = 0;
While( i <= Long(sVar) );
   sChar = SubSt( sVar, i, 1 );
   If( Scan( sChar, '-.0123456789' ) = 0 );
      nErr = 1;
      # here throw error message, strip character out, write log, etc.
   EndIf;
   i = i + 1;
End;
If( nErr = 0 );
   # variable is numeric
   nVar = StringToNumber( sVar );
   # blah blah
EndIf;
If all tests pass then the input variable is numeric, you can convert it to a number and assign to a numeric variable.
Of course the test can get more elaborate to allow for different decimal and thousand separators, enclosing brackets for negative, "SAP" trailing negative indicator, scientific notation, etc, etc. but basically it is quite a laborious process. Keeping everything completel within TM1/TurboIntegrator there simply isn't a better way.

The future I think, with tools like TM1Py, looks a little different.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: IF function return undefined type Error

Post by Drg »

wow lotsaram a nice example of finding numbers in a string.
Thank you for your answer, he really confirmed that this is a pain and not a lack of my knowledge.
You are right the future for various api services.
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: IF function return undefined type Error

Post by Wim Gielis »

Untested, just an idea that popped up.
What if you use the functions Numbr or StringToNumber or StringToNumberEx, and if the result is <> 0, then it’s a number. Otherwise, keep treating it as String.
Last edited by Wim Gielis on Thu Oct 31, 2019 10:38 am, edited 1 time in total.
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: IF function return undefined type Error

Post by lotsaram »

Wim Gielis wrote: Thu Oct 31, 2019 9:20 am Untested, just an idea that popped up.
What if you use the functions Numb or StringToNumber or StringToNumberEx, and if the result is <> 0, then it’s a number. Otherwise, keep treating it as String.
Using Numbr is OK. I do that sometimes as this will just strip ot any non-numeric characters.
e.g. Numbr('1a00b') = 100
But NumberToString not so much as this will throw an error if you call it on a string that contains non-numerics.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply