TM1 Forum

Hi All,

I have a requirement to merge multiple Dept IDs in to one based on some matching strings and sum the numeric values.
I have given a preview of my current data below. (I am also Attaching the screen shot of the file for clarity)

Current
Dept X Y P Q N1 N2
1 AA XX PQ MN 10 30
2 AA XX RS OP 20 40
3 BB YY AB GH 10 20
4 BB YY CD HI 20 30
5 BB YY EF JK 30 40

Updated
Dept X Y P Q N1 N2
6 AA XX Def Def 30 70
7 BB YY Def Def 60 90

Here Dept is a dimension, X,Y, P, Q are strings and N1 and N2 numeric measures. All are part of another dimension called 'Measures'.

I need to merge multiple depts in to single one if their X, Y string values are same and put a default value (Def) for P,Q Strings.
I also need to sum the numeric measures and the new element need to be created in dept dimension with a unique ID (which is one more than the current highest dept id)

In this case, IDs 1,2 are combined (as they have same X,Y string combination) to get ID 6 (One more than current highest id 5) and numeric measure 1 and 2 sum of N1 and N2 of Dept IDs 1,2 respectively (10+20 and 30+40)
Similarly IDs 3,4,5 are combined (as they have same X,Y string combination) to get ID 6 (One more than current highest id 6) and numeric measure 1 and 2 sum of N1 and N2 of Dept 3,4,5 respectively (10+20+30 and 20+30+40)

Any guidance on the approach with some sample code will be helpful.

I suggest desired outcome may be better served by rethinking your cube structure. It seems that the Dept dim is nothing more than a place holder to collect the numeric measures for the various combinations of X, Y, P & Q and not a real part of an organization structure?

Regardless, my thought is this: create dimensions for X, Y P & Q with their associated elements, add a consolidation to each "All Whatevers" that each element rolls in to. Load the data appropriately and you can create a view that displays the data either at the detail level of X or Y, X, Y and P/Q, etc, etc. If the need for a Dept Id is paramount, then (again not knowing your full requirements) I would consider the cube I have described being used by a TI to create another cube that has the Dept dimension. The Dep dim can be modified on the fly within the same TI to add the required Dept IDs.

While not knowing how much data you are dealing with, if you are dealing with large volumes and the X, Y, P, Q values are used dimensionally in other parts of your model, the cube I described will perform much faster and may be significantly easier to use within a dimensional model.

Hi,
Thanks a lot for the reply.

Changing the cube structure or creating a new cube cannot be done in the current structure.
I have given dept ID as an example, its just dimension containing an ID field.

Also the number of numeric and string measures which are getting captured are more. We have 200 plus such items, I have only listed 4 to make things clear. So making all of them to dimensions is not feasible.

any other suggestions are welcome

sreesuku,

Please post your actual process code, following the guidelines for posting (http://www.tm1forum.com/viewtopic.php?f=3&t=1037)
You can't expect someone to write all this for you, nor that people guess or write pseudo-code.
Please indicate what results you obtain and what is wrong/correct in these results.
You joined this board 7 years ago, we can expect at least some level of TI knowledge.

Thanks.