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.
Merging of multiple elements
-
- Posts: 47
- Joined: Wed Sep 14, 2011 7:45 am
- OLAP Product: Cognos TM1
- Version: PAW
- Excel Version: 2013
Merging of multiple elements
- Attachments
-
- Preview of data.JPG (119.54 KiB) Viewed 2894 times
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Merging of multiple elements
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.
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.
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 47
- Joined: Wed Sep 14, 2011 7:45 am
- OLAP Product: Cognos TM1
- Version: PAW
- Excel Version: 2013
Re: Merging of multiple elements
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
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
-
- MVP
- Posts: 3113
- 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: Merging of multiple elements
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.
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.
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
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