TM1 Forum

Hi everyone

I thought this would be easy but...

I have a standardised TI process to copy data between cubes - or within cubes
This is relatively simple - data source is a view - manipulate subsets and views in the Prolog and push data across in the Data tab
So far this works well and I've been able to easily deploy across a variety of situations

Now, I want to use this in a cube where the measures dimension contains both numbers and strings - n and s elements
The problem comes at the variables tab. You have to define an element as a String or a Numeric

If I choose numeric, the process fails unless I remove the string elements from the source view
I could choose string - but then I suppose I get into string to number conversions - untidy
I could define two processes - a bit fiddly

I'm sure I'm missing something here. Is there a simple solution?
Thaks for any suggestions

IanB

32 bit v9.1 sp4 with Excel 2003

Hi Ian

It might be an idea to work with 2 views. 1 for the string measures, 1 for the numeric measures.

Create a loop through the measures dimension, and check the type of element. (or a loop through a subset generated by MDX containing the leaf level elements)

Then, using SubsetElementInsert, add elements to a subset containing numeric elements or to a subset containing string elements.

Perhaps I am also overlooking something basic, but this could work and is not too untidy.

Wim

what about VALUE_IS_STRING? The wizard uses this, then you use Nvalue or Svalue for the output. Not _completely_ sure what populates these, but have a dig around. It's even documented in the help

David Usherwood wrote:what about VALUE_IS_STRING? The wizard uses this, then you use Nvalue or Svalue for the output. Not _completely_ sure what populates these, but have a dig around. It's even documented in the help

VALUE_IS_STRING is one of three "hidden" variables (i.e., they don't appear on the Variables tab) which exist when the data source is a cube view. The other two, as you've noted, are SVALUE and NVALUE, which return the cell value in the relevant format. (Obviously NValue is 0 if it's a string.) This allows you to just Ignore the actual value rather than worrying about whether to set it to Numeric or String, and use VALUE_IS_STRING in an If block to determine whether to use the NValue or SValue hidden variable to manipulate the data. The variables are created automatically (and populated automatically) just by virtue of the data source being set to a View. However they're "real" variables; you'll see them if you look at a .pro file.

It's an instance where the TI designers really thought ahead to cover just this kind of example.

Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric. These hidden variables are in every VIEW process and they are covered in the help but maybe not highlighted as much as they should be. Alternatively if these variables didn't exist or you didn't want to use them you could do something like the following which would be equivalent. As the cell type is determined by the element type of the last dimension you can test this condition. Note, for this to work you would have to set the variable type of the "Value" variable to String.

lotsaram wrote:Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric.

Nuh-uh. NValue is always populated as well, but it'll be populated with 0 if the element is a string. Presumably that's to ensure that the variable will never throw an "undefined" error if it's used by mistake on a string element.

Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.

In a very strict sense yes, where the value is not numeric the variable is initialised with a value of zero, but this is semantics, a null value in TM1 terms is for most intents and purposes the same thing as no value. Potato, potato ....

lotsaram wrote:

Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.

In a very strict sense yes, where the value is not numeric the variable is initialised with a value of zero, but this is semantics, a null value in TM1 terms is for most intents and purposes the same thing as no value. Potato, potato ....

No, there is a difference between 0 and undefined/uninitialised.

In VBA a numeric variable will automatically have a value of 0 when first used (assuming that Option Explicit isn't set). This is not the case in TI, where use of a variable which has not been assigned a value either by the process or by an earlier line of code will generate a syntax error.

So it's more like potato, tomato.

lotsaram wrote: ↑Mon Jun 08, 2009 10:19 pm Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric. These hidden variables are in every VIEW process and they are covered in the help but maybe not highlighted as much as they should be.

Code: Select all

IF( VALUE_IS_STRING = 1 );
  CellPutS( SVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ELSE;
  CellPutN( NVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ENDIF;

Alternatively if these variables didn't exist or you didn't want to use them you could do something like the following which would be equivalent. As the cell type is determined by the element type of the last dimension you can test this condition. Note, for this to work you would have to set the variable type of the "Value" variable to String.

Code: Select all

vElType = DTYPE( cDim5, vDim5 );
IF( vElType @= 'N' );
  CellPutN( NUMBR(VALUE), cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ELSE;
  CellPutS( VALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ENDIF;

i am surprised that whether or not the three variables (value_is_string /svalue/nvalue) should be declared first in the prolog，if i want to use them in the data？thankyou first

Niko wrote: ↑Sun May 22, 2022 11:03 ami am surprised that whether or not the three variables (value_is_string /svalue/nvalue) should be declared first in the prolog，if i want to use them in the data？thankyou first

You can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.

Wim Gielis wrote: ↑Sun May 22, 2022 12:44 pm

Niko wrote: ↑Sun May 22, 2022 11:03 ami am surprised that whether or not the three variables (value_is_string /svalue/nvalue) should be declared first in the prolog，if i want to use them in the data？thankyou first

You can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.

Okay，i have succeed ，thankyou verymuch

Wim Gielis wrote: ↑Sun May 22, 2022 12:44 pm

Niko wrote: ↑Sun May 22, 2022 11:03 ami am surprised that whether or not the three variables (value_is_string /svalue/nvalue) should be declared first in the prolog，if i want to use them in the data？thankyou first

You can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.

Hi,Wim Gielis:
Sorry to disturb you. I wonder if you know which version of TM1 starts to support REST API. Please let me know if you know
Thankyou first.

Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong

Wim Gielis wrote: ↑Thu May 26, 2022 7:16 am Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong

It's somewhere in 10.2.2. I could look it up but it's academic, because there were a lot of feature gaps in the 10.x iterations. To get the full API functionality requires a jump to one of the PA2 releases.

Wim Gielis wrote: ↑Thu May 26, 2022 7:16 am Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong

i get it，Thank you and Alan Kirk.