I've queried data from same sheet into different tabs based on condition that i want but how to replace blank cells with 0, on column F and G only.

The query as below:

=QUERY('pending SKUs'!E2:AQ,"select F, G, H, L, M, N, O, I where E=1 AND P <> 2 AND P <> 3 AND X='Pass' AND AQ <> 'Rejected'")

Also prepared a dummy sheet for it, can refer here: https://docs.google.com/spreadsheets/d/1DwgOVhsChyCw4NQtUyOx3q_4BoXLpBkN0U0RllNHFec/edit#gid=1530226665

CodePudding user response：

I know only one option is to use IF() function like-

=ArrayFormula(IF(QUERY('pending SKUs'!E2:AQ,"select F, G, H, L, M, N, O, I where E=1 AND P <> 2 AND P <> 3 AND X='Pass' AND AQ <> 'Rejected'")="",0,
QUERY('pending SKUs'!E2:AQ,"select F, G, H, L, M, N, O, I where E=1 AND P <> 2 AND P <> 3 AND X='Pass' AND AQ <> 'Rejected'")))

CodePudding user response：

If your query is only generating numeric data, then you can use the N function to transform the blanks into zeros without having to refer to the query twice as per the IF approach:

=ARRAYFORMULA(N(QUERY('pending SKUs'!E2:AQ,"select F, G, H, L, M, N, O, I where E=1 AND P <> 2 AND P <> 3 AND X='Pass' AND AQ <> 'Rejected'")))

N.B - ARRAYFORMULA is also needed to iterate N over the whole query array.