coalescing data in a table
I'm attempting to build a table, which pulls from a order history dataset and includes the following key columns: Account Number, Order Src (CSR, EDI, or DCX).
When I create beastmodes (e.g. CSR $ = case when `Order Src` like 'CSR' then sum (`order_line_total_price`) end) to break down the orders for each account by their order sources... it doesn't include them all on one line for each account.
As shown below, you can see for Account # 12000001244... it has three different rows (one for each CSR, DCX, and EDI). How can I get all of them on one row for this account (and all the other accounts)?
You need to put your condition inside your aggregation.
SUM(CASE WHEN `Order Src` LIKE 'CSR' THEN `order_line_total_price`) END)
Also - as a side note your LIKE 'CSR' isn't matching on any wild cards so it's expecting Order Src to be equal to exactly CSR. if you're wanting to see if CSR is anywhere inside the Order Src value then you'd need to put in the wildcards
You'll also need to make sure you aggregate the other columns you wish to summarize for each account**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Thanks. when I removed the last order (by each source) beastmodes, this worked.
However, I have the following beastmodes for each source: Last CSR Order = case when `Order Src` like 'CSR' then max(`order_date`) end
And when I include them, they seem to mess up the table... is there a way to fix that formula so the issue doesn't arise?0
Whenever you want to do an aggregation you need to put your function on the outside of your case when statement. If you do case when 'x' then max(date), the beast mode just does the calculation for each individual line, so it will just take the max date of each row in your dataset. If you instead do MAX(case when 'x' then 'date') then the beast mode will treat that as an aggregation and will look for the MAX date in your table groupings.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 77 Cards, Dashboards, Stories
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive