TM1 Forum

Hi all,

I used CellGetN to retrieve data from a cell but it didn't work.
My cube : Invesment
Dimensions in cube :

• Year
  Month
  Department
  Measures

Measures contains : Amount(number) & Note(string). I just want to get Amount so my function is:
CellGetN ('Investment',Year,Month,Department,'Amount');

I used process with TM1 view as a datasource. CellGetN is written in Data tab.
The process run and both Amount and Note is retrieved => error due to Note is string.

Thanks for your help,
Tin.

phamtinkt88 wrote:Hi all,

I used CellGetN to retrieve data from a cell but it didn't work.
My cube : Invesment
Dimensions in cube :

• Year
  Month
  Department
  Measures

Measures contains : Amount(number) & Note(string). I just want to get Amount so my function is:
CellGetN ('Investment',Year,Month,Department,'Amount');

I used process with TM1 view as a datasource. CellGetN is written in Data tab.
The process run and both Amount and Note is retrieved => error due to Note is string.

Thanks for your help,
Tin.

I know that this isn't the question that you're asking but without asking a thousand questions about how the view is defined, how your variables are defined as and so on, it's still the easiest way to get you from A to B.

Redefine the view that you're using as your data source. Ensure that the Measures dimension uses a subset consisting of the element "Amount" only. In this way every value that you are fed from the view will be the Amount, and using CellGetN will be redundant. The Amount value will be the value that appears in the relevant variable.

Alan Kirk wrote: I know that this isn't the question that you're asking but without asking a thousand questions about how the view is defined, how your variables are defined as and so on, it's still the easiest way to get you from A to B.

Redefine the view that you're using as your data source. Ensure that the Measures dimension uses a subset consisting of the element "Amount" only. In this way every value that you are fed from the view will be the Amount, and using CellGetN will be redundant. The Amount value will be the value that appears in the relevant variable.

But if i only retrieve Amount base on some condition in Note (E.g: only get amount if note contains "good") I have to create 2 view. And if i retrieve value base on many conditions, i have to create a lot of view. Is there any better way to do that ?

Thanks for your reply !

The key thing here is that there is one row processed in the data tab for each cell in the view. That means that you get a row for each note and a row for each amount. If you do as Alan suggests and restrict your view only to the amount you can still use the other dimension coordinates to use CellGetS to retrieve the corresponding note to each amount.

I agree entirely with above comments that you should use the view to define your values... otherwise a cube view as a datasource is pretty pointless but on note of the original question you have already answered it yourself...

CellGetN does not work for "Note" because "Note" is a string value, CellGetN is used to retreive Numeric values... CellGetS is the string counterpart.

Alan Kirk wrote: I know that this isn't the question that you're asking but without asking a thousand questions about how the view is defined, how your variables are defined as and so on, it's still the easiest way to get you from A to B.

Redefine the view that you're using as your data source. Ensure that the Measures dimension uses a subset consisting of the element "Amount" only. In this way every value that you are fed from the view will be the Amount, and using CellGetN will be redundant. The Amount value will be the value that appears in the relevant variable.

I'd reverse it and configure the view to pull only Note. IF(Note@='good') do a CellGetN plus whatever other processing. Probably doesn't materially affect performance for most models, unless it's a monster, but this saves some CellGetx calls (assuming you consider the original view to be a series of CellGetx). If you pull Amount in the view, you always have to also CellGetS for Note for every Data tab cycle-- this way you only CellGetN when Note@='good'.

Matt