#40 — Group And Summarize A Tree Structure Table

Problem description & analysis:

There's a tree structure Excel table, where the Epic column is the highest data layer.

Task: Group rows by Epic, the highest data layer, and summarize the Hours column while keeping the Code column. Below is the expected result:

Solution:

Use SPL XLL to perform the computation:

=spl("=E(?1).group@i(Epic!=null).new(Code,Epic,ifn(~.sum(Hour),0):Hours)",A1:E10)

As shown in the picture below:

Explanation:

group@i function performs the conditional grouping. Symbol ~ represents the current group; new()function creates a new table; ifn() function returns the first non-null member (return 0 when the aggregation result on the current group is null).

Comments

[Judith-Excel-Sharing]

We’re gonna share more valuable insights and tips with fellow coders and learners in our communities, so feel free to join us🫶🏻
💫Discord: discord.gg/hgbKEvJ4
💫Reddit: reddit.com/r/esProc_Desktop/

[Judith-Excel-Sharing]

Feel free to download esProc Desktop and boost the productivity of your data processing tasks ⬇️

🔎SPL download address: scudata.com/download-Desktop

📊Plugin Installation Method: c.scudata.com/article/1652061135502

🔎References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

📊SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...