Partition BY, filling missing values
Hello,
I have sales data and want to do some calculations on reference quote numbers assigned to those sales. Sales data have unique 'docnum' and there might be 5-15 rows assigned to the same 'docnum', depending on how much equipment is being ordered. If there is a quote behind an order, then 'Quoteref' will have the respective quote number assigned, if there is not quote then the field will remain null. Unfortunately, the query I'm using has some flaws and some of the orders that have quote behind it, don't populate it correctly. For example, some orders that have 10 rows of data will only populate 'Quoteref' for 8 rows ( 2 missing). Tried working with ChatGPT to by they suggest to do Partition BY which is supposedly not supported by Domo.
Any way to solve that issue to populate all "QuoteRef' for a respective order when it is missing?
Thank you
Best Answer
-
I'd Group By
docnum
to produce the 'First non-null value' ofQuoteref
and then Join the result of that Group By to the originalsales
data ondocnum
.
If there's any possibility of multiple distinctQuoteref
values within a givendocnum
and you don't want to lose that information, you could do a formula aggregate instead using an expression likeGROUP_CONCAT(DISTINCT Quoteref SEPARATOR ', ')
Randall Oveson <randall.oveson@domo.com>
0
Answers
-
up, anyone got an idea?
0 -
I'd Group By
docnum
to produce the 'First non-null value' ofQuoteref
and then Join the result of that Group By to the originalsales
data ondocnum
.
If there's any possibility of multiple distinctQuoteref
values within a givendocnum
and you don't want to lose that information, you could do a formula aggregate instead using an expression likeGROUP_CONCAT(DISTINCT Quoteref SEPARATOR ', ')
Randall Oveson <randall.oveson@domo.com>
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive