Partition BY, filling missing values

zuchu
zuchu Member
in Magic ETL

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

  • rco
    rco Domo Employee
    Answer ✓

    I'd Group By docnum to produce the 'First non-null value' of Quoteref and then Join the result of that Group By to the original sales data on docnum.

    If there's any possibility of multiple distinct Quoteref values within a given docnum and you don't want to lose that information, you could do a formula aggregate instead using an expression like GROUP_CONCAT(DISTINCT Quoteref SEPARATOR ', ')

Answers

  • zuchu
    zuchu Member

    up, anyone got an idea?

  • rco
    rco Domo Employee
    Answer ✓

    I'd Group By docnum to produce the 'First non-null value' of Quoteref and then Join the result of that Group By to the original sales data on docnum.

    If there's any possibility of multiple distinct Quoteref values within a given docnum and you don't want to lose that information, you could do a formula aggregate instead using an expression like GROUP_CONCAT(DISTINCT Quoteref SEPARATOR ', ')

Categories