Is there a way to do this in Magic ETL?
I've got the following CASE statement in a beast mode now, wondering if there is a way I can do in Magic ETL so I can then use in card filters.
CASE
WHEN SUM(`Order Quantity`) > SUM(`Quantity Available`) THEN SUM(`Value Available`)
ELSE SUM(`Order Value at 02 Slow Item Cost`)
END
Thanks in advance for your help!
Comments
-
How are you wanting your card filter to behave? It's possible you may not need to do an ETL.
0 -
For example, would want to filter where the result is >$5000 only.
Regardless, would like to know how to do this in Magic ETL if possible.
Thank you
0 -
Hopefully someone else can chime in on the ETL portion. But you can create the filter by creating another BeastMode
CASE WHEN
CASE
WHEN SUM(`Order Quantity`) > SUM(`Quantity Available`) THEN SUM(`Value Available`)
ELSE SUM(`Order Value at 02 Slow Item Cost`)
END
> 5000 THEN '> 5k'
ELSE '< 5k'
ENDThat would let you filter between > 5k or < 5k.
0 -
Ever get an answer to this? Looking for the same thing.
1 -
It turns out that this question is a little trickier than it may seem on the surface. Any time we are using an aggregate function (SUM, in this case) there are decisions that need to be made about how the data is being grouped and filtered in order to give you that aggregation.
For example, in the original formula that was provided:
CASE
WHEN SUM(`Order Quantity`) > SUM(`Quantity Available`) THEN SUM(`Value Available`)
ELSE SUM(`Order Value at 02 Slow Item Cost`)
ENDThere is an assumption that the data is being grouped by item, time frame, etc., and may be filtered by time frame, region, warehouse, etc.
If those groupings and filters are going to be hard-coded into the setup of the card, then we can use a dataflow with matching groupings and filters to do what you're after. If the groupings or filters will change on the fly, then it will no longer make sense to create the dataflow to accomplish this.
Taking the idea a little further, if the grouping and filtering are set and won't be changed, then it probably make sense to use a dataflow to create a dataset that is already filtered and aggregated to that level. Then the SUM functions wouldn't be needed in the beast mode and it would work to use it as a filter.
There is a post in the ideas exchange (link below) regarding adding aggregate filters to the Analyzer. I'd recommend upvoting this idea and providing feedback regarding the need for this type of filtering.
https://dojo.domo.com/t5/Ideas-Exchange/Aggregate-Beast-Mode-Filter/idi-p/14924
I hope that helps!
1 -
I agree with ZCameron, you need to ask if putting the aggregate in a DataFlow is the correct place or not.
I would also suggest you go like the Idea to get it added to the product.
To answer your question in the basics, you can do a case statement in Magic. It isn't very pretty, but it works. See image for an example:
Case Statement using Filters
You simply create a Filter for each of the case statement, then do the aggregate then append them back.
So here you see a Filter for HN, FR, SA, and LE. Then you see an Else Filter to catch anything else. So the Case statement will look this:
Case
When CountryCode = 'HN' then Sum(Price)
When CountryCode = 'FR' then Sum(Price)
When CountryCode = 'SA' then Sum(Price)
When CountryCode = 'LE' then Sum(Price)
Else Sum(Price)
EndThe Sum(Price) can be any functions you need to take care of.
Remember to think of the Else statement and make sure you don't overlap in your Case Filters.
Once you have down the calculations on the filtered data, you just append it back together.
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