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.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 435 Beast Mode
- 22 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive