Group columns without stacking data

Kimber
Kimber Member
edited September 2022 in Magic ETL

Is there a way to group numeric columns without summing the data? I want to group five columns, but simultaneously be able to view the data for each individual column in the group.


In my dataset, I have 15 columns of data: five columns represent the past populations of five bird species, five columns represent present populations of the same species, and the final five columns represent the future populations.

I want to be able to create a chart which compares the past, present and future populations. This means I want to aggregate the five rows of "past," five rows of "present," and five rows of "future."

When I used CONCAT, the result has been a sum of all five species. However, I do not want a grand sum. I want to be able to view the data for each of the five individual species in each of the three aggregations.

In other words, our data appears as follows:

Column 1: # of Species 1-Past

Column 2: # Species 2-Past

Column 3: # Species 3-Past

[etc...]

Column 6: # Species 1-Present

Column 7: # Species 2-Present

[etc...]

Column 15: # Species 5-Future


In our older dataset, we had it organized as follows:

Column 1: variables = past/present/future

Column 2: Species 1

Column 3: Species 2

Column 4: Species 3

Column 5: Species 4

Column 6: Species 5


This older format worked how we wanted it to, and allowed us to compare past/present/future population counts per species. However, our old dataset had more than ten million rows of data, and we want to shorten the length of the dataset, so we'd prefer if we can figure out a way to get the new format to work.

Answers

  • i'm not sure if i understand what you're trying to accomplish.

    it sounds like you're trying to create a sum across rows. that's easy with a formula or beast mode (col1+col2+col3+col4).

    if you want to use the past n rows, you might look at using the LAG() function in the RANK and WINDOW tile. it's a google-able operation just google "Lag + window function" that might get you in the right place.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"