Rank and Window: Unbounded following
Hello,
Is there an equivalent way to count rows in a partition with unbounded preceding and unbounded following? I see that "unbounded" is available in the 'preceding' option but not in the 'following'.
I simply want to count all the rows within the specified partition and reflect to the respective rows, but the step 4 isn't optional which I thought should be, Is there any alternative to this in the Magic ETL?
Thanks.
Best Answer
-
You can accomplish by using the group by tile to count the number items in each domain and then join it back to your original dataset and join on domain. The ETL would look something like this.
**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.0
Answers
-
If you select unbounded for preceding and enter 0 for following, that will give you a running total by partition. Is that what you are wanting?
**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.0 -
@MarkSnodgrass, thanks for responding. However, that's not what I'm going for. What I wanted instead is to just count everything within the partition for every row. If this is SQL, I would've simply written it as COUNT(invoice_item_model) OVER (PARTITION BY end_user_domain)
The problem is in Magic ETL, it seems the frame range function is required even if it's not needed, and the unbounded option is not available for the "following" function.
Here's an example of what I wanted to achieve.
invoice_item_model, end_user_domain, COUNT()
A, domain1, 3
B, domain1, 3
C, domain1, 3
D, domain2, 2
E, domain2, 2
F, domain3, 1
0 -
You can accomplish by using the group by tile to count the number items in each domain and then join it back to your original dataset and join on domain. The ETL would look something like this.
**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.0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive