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 ', ')
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 ', ')
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 544 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 345 Distribute
- 87 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 8 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive