How can I remove items from a mega table using a beast mode?
I have a table with 5 columns: The first column (A) lists parts, the second (B) lists countries, the 3rd (C) is a concatenation of the first 2 columns, and the 4th (D) is a concatenation of the same list of parts but other related countries. There are many more countries in column D than in column C, so most of the values in column C are duplicated multiple times. The values in column D do not duplicate. Column E indicates if there is a match between columns C & D.
How can I write a beast mode that will remove all the rows in the mega table for the matching country from column B? (In the picture, yellow highlights the matching row and the red indicates the rows that need to be deleted)
Best Answer
-
You'd likely need to utilize a window function and a case statement. Something like this might work for you
MAX(MAX(CASE WHEN `Country1` = `Concat Field` THEN 1 ELSE 0 END)) OVER (PARTITION BY `Part1`, `Country1`)
Then use this field in the filter to ignore values of 1.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
how do you know which row you want to exclude?
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"1 -
Every row that is highlighted in red has Egypt in the second column and needs to be removed. The Egypt rows need to be removed because they match the Egypt in the last column. The last column is a based on a beastmode case statement that concatenates a part number with a country when columns 3 and 4 match. The last column may be optional. I had theorized that the last column would be used as a trigger to eventually contribute to a larger beastmode that would be needed to remove all the rows with a country that matches in columns 3 and 4. As an FYI, I only recently got permission/privileges to actually build datasets/dataflows and am inexperienced in doing so. I am fairly experienced in building beastmodes, and would prefer to make this work using a beastmode, if possible.
0 -
You'd likely need to utilize a window function and a case statement. Something like this might work for you
MAX(MAX(CASE WHEN `Country1` = `Concat Field` THEN 1 ELSE 0 END)) OVER (PARTITION BY `Part1`, `Country1`)
Then use this field in the filter to ignore values of 1.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Grant, your idea work!!! Thank you
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive