Merging of multiple elements
Posted: Wed Jul 04, 2018 1:36 pm
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 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.