How to get aggregated data without losing rows

I am looking to get count for each row without loosing any row.

It can be achieved easily in excel using count if in excel but here how I can implement in ETL or Beast mode?


Please see the sample input and output I am looking for in below image


Best Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @user094816 , no if you create a beast mode on an aggregate, no you should not try to build a calculated column off of it. Sometimes it works, usually it gets weird.


    you can create this in ETL using a GROUP BY with a COUNT, and then JOIN the GROUP BY tile back to the ungrouped data.

    from there you can easily build a CASE statement using a Formula tile in Magic 2.0


    If you don't have Magic 2.0, you can still use the GROUP BY appropach and then either implement the CASE statement using a blend of FILTER and APPEND tiles, or in a beast mode.

    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"
  • MarkSnodgrass
    Answer ✓

    @user094816 you can do this very easily in the ETL. See example in the image below.

    Start with your dataset and then add a Group by tile and do a count of your column you want counted.

    Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.

    This will keep all your rows and show the total from the group by tile as well.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Hi @user094816

    You can utilize a window function in a beast mode - you need to have this feature enabled in your instance. Talk with your CSM to enable window functions.


    SUM(SUM(1)) OVER(PARTITION BY `Col1`)
    

    This would get you 5 for every ABC123 record. In your example data you have ABC123 listed 4 times and then 1 time but the number of repeated is 4 for all 5 entries. Are you expecting it to be 4 or should it actually be 5?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hi @GrantSmith , that was typo, I want it to be 5 only. This function works great in Beast mode. (can we create this in ETL?)

    I have got another problem. Now I am not able to refer the output of this function to create custom Column in beast mode.

    plz see the latest screenshot.

    In Excel I have formula like this to achieve last column:

     =IF(AND('Count no of Repeated'>1,'Channel'<>"Test Channel"),"Flag1","")

    if above measure you mentioned can be created in ETL then I guess I can reference that column but right now it is beast mode output and I am not able to refer this one to another function.


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @user094816 , no if you create a beast mode on an aggregate, no you should not try to build a calculated column off of it. Sometimes it works, usually it gets weird.


    you can create this in ETL using a GROUP BY with a COUNT, and then JOIN the GROUP BY tile back to the ungrouped data.

    from there you can easily build a CASE statement using a Formula tile in Magic 2.0


    If you don't have Magic 2.0, you can still use the GROUP BY appropach and then either implement the CASE statement using a blend of FILTER and APPEND tiles, or in a beast mode.

    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"
  • MarkSnodgrass
    Answer ✓

    @user094816 you can do this very easily in the ETL. See example in the image below.

    Start with your dataset and then add a Group by tile and do a count of your column you want counted.

    Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.

    This will keep all your rows and show the total from the group by tile as well.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks!! @MarkSnodgrass @jaeW_at_Onyx @GrantSmith . Now I am able to achieve what I wanted using ETL to generate the count and Beast mode to created calculated column based on output from ETL logic.