14.9 Expand into multiple columns horizontally

 

The following data table stores a variety of parts and their metal subparts in an orderly manner. When Level=2, it indicates that this row is the part row (aggregation row), and when Level=3, it indicates this row is the subpart row. The columns Proportion and Material store the proportion and name of metal for the subparts respectively.

imagepng

The task is to put the total proportion of each metal on the right side of aggregation row:

imagepng

Enter in cell E1:

A
1 =E(‘A1:D12’)
2 =A1.id(Material).select(~)
3 =A1.derive(${A2.concat@c()})
4 =A3.group@i(Level==2)
5 =A4.run(~.to(2,).groups(Material;sum(Proportion):value).run(A4.~(1).field(Material,value)))
6 =A3.new(${A2.concat@c()})

A2: Find out the type of Material that is unique and nonnull
A3: Append one column for each type of Material found in A2
A4: Group A3, and create a new group when Level is 2. The option @i means creating a new group when the condition is met
A5: Loop through each group of A4, and count the sum of proportions of each material by the Material group from the second row to the last row, and name it the value column, and loop through each group, and assign Material column of the first row of the current group in A4 to value
A6: Select the required columns from the results in A3 and return


esProc Desktop and Excel Processing
14.8 Insert blank row when meeting with data change
14.10 Expand into multiple N-column horizontally