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.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