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
-
@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"0 -
@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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
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!**1 -
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.
0 -
@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"0 -
@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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
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.
0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive