Using Arithmetic Operators for null values in GSheets Query Function

Question

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.

Do NOT share spreadsheets/images 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 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, if you share Google files. — TheMaster
– TheMaster, Commented Nov 4, 2022 at 14:37

Osm · Accepted Answer · 2022-11-04 14:59:38Z

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

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.

edited Nov 4, 2022 at 14:59

answered Nov 4, 2022 at 13:31

Osm

2,9112 gold badges8 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Content_Type6 Over a year ago

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.

Collectives™ on Stack Overflow

Using Arithmetic Operators for null values in GSheets Query Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related