Group by Value in Column

ewelsh
ewelsh Member
in Magic ETL

Hi All, 

 

I'm looking to find a way to group a dataset based on a specific value in a column.

 

Any ideas how to do this?

 

Thanks!

 

Eric

Best Answers

  • user07652
    user07652 Member
    Answer ✓

    I don't know if I am doing it right, but using the following calculated field seems to be showing results similar to what I was expecting.  I added this as a calculated field and then graphend the COUNT of this over time.

     

    COUNT(DISTINCT `ColumnName`)

  • zcameron
    zcameron Domo Employee
    Answer ✓

    Yes, that is correct. COUNT is an aggregate function, just like SUM, AVG, MAX, etc. Adding the DISTINCT modifier will ignore duplicates of any values that have already been included in the count. 

     

    Well done!

Answers

  • zcameron
    zcameron Domo Employee

    Hey Eric,

     

    I'm sure we can help you work this out. Where are you hoping to do this grouping (on a card, in a dataflow, etc.)? It sounds like you're hoping to take a dataset and return some values that have been aggregated based on having the same value in a certain column. For example, if your data is like this:

     

    id|Series|Value 

    1|Alpha|7

    2|Alpha|4

    3|Alpha|9

    4|Beta|3

    5|Beta|7

    6|Charlie|4

    7|Charlie|5

     

     

    You'd like to be able to get an output dataset with the Value column aggregated grouped by the Series values like this:

     

    Series|Value

    Alpha|20

    Beta|10

    Charlie|9

     

    Is that right? If so, you're in luck because there are a lot of good ways to accomplish that. If not, can you clarify so we can know what you're hoping to see?

     

  • Unknown
    Unknown

    @ewelsh, did the above reply help you out?

  • yadumath
    yadumath Member

    I have the same question. Suggestions on how to do this?

  • RGranada
    RGranada Contributor

    Hi,

     

    Can you clarify what you need and where you need to do it?

     

    Regards,

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • yadumath
    yadumath Member

    I have a table combining two Google Analytics data sources, which is structured like this:

     

    Event Action|Event Category|Evant Lablel|Pageviews|Hostname+Pagepath

    A                            B                       C                  1        test1.com+ /shop/page1

    D                           E                       F                   1        test1.com+ /shop/page1

    G                           H                       I                    1        test1.com+ /shop/page1

    J                            K                       L                   2        test2.com+ /shop/page2

    M                           N                       O                  2        test2.com+ /shop/page2

     

     test1.com+ /shop/page1 has 1 pageview and 3 events recorded from that 1 pageview. Problem is that pageviews get repeated multiple times for each combination of Action/Category/Label and when i sum pageviews I get an incorrect sum.

     

    Desired output:

    Event Action|Event Category|Evant Lable||Pageviews|Hostname+Pagepath     | Pageview_correct

    A                            B                       C                  1        test1.com+ /shop/page1                1

    D                           E                       F                   1        test1.com+ /shop/page1                 

    G                           H                       I                    1        test1.com+ /shop/page1

    J                            K                       L                   2        test2.com+ /shop/page2                 2

    M                           N                       O                  2        test2.com+ /shop/page2

     

    I want to group pageviews in such a way that it gives me the first value or average for each Hostname+Pagepath combination. Rest are left blank or set to 0. How do I do this?

     

     

     

  • user07652
    user07652 Member

    @zcameron,

     

    I share this issue as well.  I have data that has multiple rows per "Series" as you've shown.  What I want to often do is graph the count of unique things in that Series over time.  I don't need to aggregate the "Value" as in your example.  I just need to be able to end up with a measure that is the count of unique values in a column so that I can graph that over time.  What's the best way to do that?

  • user07652
    user07652 Member
    Answer ✓

    I don't know if I am doing it right, but using the following calculated field seems to be showing results similar to what I was expecting.  I added this as a calculated field and then graphend the COUNT of this over time.

     

    COUNT(DISTINCT `ColumnName`)

  • zcameron
    zcameron Domo Employee
    Answer ✓

    Yes, that is correct. COUNT is an aggregate function, just like SUM, AVG, MAX, etc. Adding the DISTINCT modifier will ignore duplicates of any values that have already been included in the count. 

     

    Well done!

Categories