Using Arithmetic Operators for null values in GSheets Query Function-CodePudding

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.

CodePudding user response：

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%'"))

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.