1

Looking to aggregate a total in a query function pivot result. However, where there are null values in Col2 and Col3 after I pivot, the total value results in null. How can I substitute null values in the query function for zero values to allow the arithmetic operator to tally the correct result in the pivoted result?

=Query(QUERY(sampledata,"select D, COUNT(C) where A = 'Supplied' AND M = 'Recommended' group by D pivot B order by D"),"Select Col1,Col2,Col3,Col3+Col2,(Col2/(Col3+Col2)) label Col3+Col2 'Total', (Col2/(Col3+Col2)) 'Rate' format Col1 'dd-mmm-yyyy', Col2 '#,##0', Col3 '#,##0', Col3+Col2 '#,##0', (Col2/(Col3+Col2)) '#,##0.0%'")

Attempted to use normal SQL functions like ISNULL and COALESCE

COALESCE(Col3, 0) ISNUL (Col2, 0)

However, these don't work in GSheets.

Content_Type6
  • 13
  • 3
  • Share your data sample – Osm Nov 04 '22 at 13:54
  • Please include your desired results. – Osm Nov 04 '22 at 14:14
  • Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data, to avoid closure of the question. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Also, note that [your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. – TheMaster Nov 04 '22 at 14:37

1 Answers1

0

Modify your formula like this

=ArrayFormula(Query(LAMBDA(q, IF(q="",q*1,q))(QUERY(Sheet1!A1:M,"select D, COUNT(C) where A = 'Arrived' AND M = 'Yes' group by D pivot B order by D")),"Select Col1,Col2,Col3,Col3+Col2,(Col2/(Col3+Col2)) label Col3+Col2 'Total', (Col2/(Col3+Col2)) 'Rate' format Col1 'dd-mmm-yyyy', Col2 '#,##0', Col3 '#,##0', Col3+Col2 '#,##0', (Col2/(Col3+Col2)) '#,##0.0%'"))

enter image description here

Replacing "Null" with 0, simplified like this.

ArrayFormula(...IF(QueryOutput="",QueryOutput*1,QueryOutput)...

Using lambda like this

=ArrayFormula(Query(LAMBDA(q, IF(q="",q*1,q))(QueryOutput)...))

q is just a lambda() name.

Osm
  • 2,699
  • 2
  • 4
  • 26
  • Thanks, Osm, for the feedback. The null values don't exist in the sample data, they exist after I count and pivot the underlying values. It results in null values in the pivoted table, which I can't tally for totals or use other operators against. – Content_Type6 Nov 04 '22 at 13:40