Add value from an element to other through TI

[jviegas@bi4all.pt]

Hi all,

I admit that maybe this is an easy one but I'm killing myself with it.

Already searched some posts but didn't get a solution

I have a cube "X" with 5 dimensions where one is formated through MDX that controls if a element in DIM "Calc" is diferent then 0 (Element "A") and a string element (element "S") has something written.

the DIM "Calc" has also the Element "B" that is numeric.

What I need is the process to go to the cube and through the MDX expression selection add "B" with "A"

Thank in advance,

Jorge

[jim wood]

Have you tried downloading the bedrock processes? If memory serves there is a process in there to do this exact thing.

[declanr]

I wouldn't bother using the MDX in the TI (you could do if you wanted)... but i'd just use a source view with all consolidations suppressed, skip zeroes, skip rules if required (depends if A is rule driven or not), just have measure "A" in the view.

On the data tab:

Code: Select all

If ( CellGetS ( 'cube', v1, v2..., 'S' ) @= '' );
         ItemSkip;
EndIf;

NElementB = CellGetN ( 'Cube', v1, v2..., 'B' );

Then just add B to your source value and do whatever you want with it.

If you do want to use the MDX in your source view be sure to make it a static subset in the prolog.

[BariAbdul]

Code: Select all

If ( CellGetS ( 'cube', v1, v2..., 'S' ) @= '' );
         ItemSkip;
EndIf;

Sorry Declan why above bit,Couldn't be this enough: SourceValueA+NElementB = CellGetN ( 'Cube', v1, v2..., 'B' );

Thanks

[declanr]

Code: Select all

If ( CellGetS ( 'cube', v1, v2..., 'S' ) @= '' );
         ItemSkip;
EndIf;

Sorry Declan why above bit,Couldn't be this enough: SourceValueA+NElementB = CellGetN ( 'Cube', v1, v2..., 'B' );

Thanks

As per the OP's requirement:

and a string element (element "S") has something written.

The item skip statement causes the TI to ignore where the string element is blank as opposed to using MDX.

[BariAbdul]

Thanks for the explanation, declanr.

[jviegas@bi4all.pt]

Hi Declanr,

Thanks for the feedback...this is actually killing me completly.

Tried your approach and its giving me an error on its execution in the DATA step:

"... Error: Data procedure line (0): Cannot cojnvert field number 6, value "Test" to a real number."

"Test" was something I've placed in the text column to run the process and it seems like its passing it to all the elements in the target when I just need to add an elemnet into another element.

I'm using this on DATA placing all elements in the variables as "Other":

If ( CellGetS ( 'Cube',ACCOUNT,CALENDAR,ORG,PRODUCTS,'S' ) @= '' );
ItemSkip;
ELSE;
NCellOldValue=CellGetN('Cube',ACCOUNT,CALENDAR,ORG,PRODUCTS,'A');
CellPutN(VALUE+NCellOldValue, 'Cube', ACCOUNT,CALENDAR,ORG,PRODUCTS,'B');
ENDIF;

I've taken a look to the code in Bedrock and although it seems a bit more complete, when I run its giving me also a strange message :

"Error: Prolog procedure line (0): Unable to open data source <Cube name>. "
(but I can open and run a "normal" data process connected to the cube (only this one fails).

Regards and thank you for the time and help,

Jorge

[BariAbdul]

This sounds quite similar to the one I encountered ,Please have a look:
http://www.tm1forum.com/viewtopic.php?f=3&t=10670 Thanks

[jviegas@bi4all.pt]

Going to check what they say on the post.

The strange thing is that I'm looking at the string column only for the IF and using "A" and "B" in the cellputn and both are numeric.

This one is really getting on my nerves and then it will be a tiny detail in the code as usual...

[tomok]

Post screen shots of each tab in the TI. Then maybe someone can actually see what you are doing and provide an answer instead of tap dancing around incomplete information.

[jviegas@bi4all.pt]

MDX subset was giving me the error on the process using the bedrock addapted.

The previous TI code was duplicating me values when adding the previous value to the new one.

The I got an error similar when I was early checking this process and I recall that was something of having a MDX expression in a view (actually a dimension of the view). So I've created two views : one with the source and the other with the target + the string element that will act like a flag and the process started to work.

The data tab is like this now:

Code: Select all

### Version Target Dimension ###

If( nDimensionIndex = 1 );
     v1 = pTargetElement;
ElseIf( nDimensionIndex = 2 );
     v2 = pTargetElement;
ElseIf( nDimensionIndex = 3 );
     v3 = pTargetElement;
ElseIf( nDimensionIndex = 4 );
     v4 = pTargetElement;
ElseIf( nDimensionIndex = 5 );
     v5 = pTargetElement;
EndIf;

### Write data from source version to target version if Follow-Up Information is present and SUM has differences to SAP Balance ###

If( pDebug <= 1  & CellGets ( cCube, v1, v2, v3, v4, pControlElement)@<>'' & CellGetn ( cCube, v1, v2, v3, v4, pDiffElement)<>0);
     #nTargetFlag=
     If( CellIsUpdateable( cCube, v1, v2, v3, v4, v5 ) = 1 );
          sElType = DType( sDim5, v5 );
          nCurrentValue=CellGetn ( cCube, v1, v2, v3, v4, pTargetElement);
          CellPutN( nCurrentValue- Value , cCube, v1, v2, v3, v4, v5 );
     EndIf;
EndIf;

### End Data ###

Now I'm only wondering is the use of subsets with MDX expressions will represent a limitation (I never got this error before).

For now I think I have the problem solved. Only have to create temp views for the process to make it more clean in the server (and to be able to destroy them at the end) and continue with the rest of the work

Thanks to all for the help and ideas that got me close to the solution.

Krs,

Jorge