how can I exclude one min and one max in a dataset
basically, its a survey dataset that have a score of 1 to 10..
every quarter, we need to exclude 1 lowest and 1 highest.
currently we are doing the manual exclusion of the lowest and highest, we're wondering if we can do it in beastmode
Comments
-
I don't think you'd be able to do that in beastmode unless I'm not understanding something. You'd have to do it in ETL.
Say MySQL, If the surveys come over with some ID you could do something like this...check my syntax and logic...but theory should be good. My thought here is you may have multiple surveys with the same lowest and highest value.
transform 1 - determine the value
select min(score) as `min score`, max(score) as `max score` from surveylist
transform 2 - find low
select top 1 `survey ID`
from surveylist as l, transform1 t
where l.score = t.minscore
transform 3 - find high
select top 1 `survey ID`
from surveylist as l, transform1 t
where l.score = t.highscore
select * from surveylist
where `survey ID` not in (select `survey ID` from transform2) -keeps 1 survey that had the lowest out
and `survey ID` not in (select `survey ID` from transform3)-keeps 1 survey that had the highest out
Thanks
Matt
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive