Coalesce for a missing month?
I can't seem to get a magic to work, nor can I seem to get a beast mode to work.
I am sorting data by month. If though, my data does not have data for May (5) then I want there to be a row for May, even if there isn't data.
Somewhat obviously, a case statement wont work because again, when there is no data, then no row.
But, how do I get a row for May? Do I use coalesce?
What if I have no data for May and August?
CASE
WHEN MONTH(AccountingPeriod
) = 1 THEN 1
WHEN MONTH(AccountingPeriod
) = 2 THEN 2
WHEN MONTH(AccountingPeriod
) = 3 THEN 3
WHEN MONTH(AccountingPeriod
) = 4 THEN 4
WHEN MONTH(AccountingPeriod
) = 5 THEN 5
WHEN MONTH(AccountingPeriod
) = 6 THEN 6
WHEN MONTH(AccountingPeriod
) = 7 THEN 7
WHEN MONTH(AccountingPeriod
) = 8 THEN 8
WHEN MONTH(AccountingPeriod
) = 9 THEN 9
WHEN MONTH(AccountingPeriod
) = 10 THEN 10
WHEN MONTH(AccountingPeriod
) = 11 THEN 11
WHEN MONTH(AccountingPeriod
) = 12 THEN 12
else AccountingPeriod
END
Answers
-
@Canio Coalesce is very helpful for null handling, but it cannot add new rows to your data. Some chart types (like bar charts) have an option to fill in missing dates if a date field is used in the X axis. Otherwise, you will need to create a separate period mapping dataset to join or append in a dataflow and add the missing rows.
0 -
@MichelleH ,
That's what I thought. Outer joins in Magic don't work, so I was hoping I could use a beast mode. Thank you0 -
If you want to append row you wouldn't do a JOIN but use the APPEND tile instead, just make sure you format your data to include any datapoints you want and any possible combinations by which your users might need to be able to filter on, otherwise, as soon as a filter gets applied to the dataset, your FILLED rows will just banish.
0 -
@MichelleH I was able to get what I needed. It just took a little work.
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
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 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