Using IF conditional in SELECT statement of Google Sheets Query function

0
votes

In order to cleanse some outlying bad data I need to implement a conditional statement into the SELECT statement of a query in Google Sheets, but GS does not want to cooperate

My attempted statement:

=QUERY('JSON Data'!A1:AS,"Select A, IF(SUM(AP)/SUM(AS) > 1,1,SUM(AP)/SUM(AS)) where B is not null GROUP BY A pivot B label IF(SUM(AP)/SUM(AS) > 1,1,SUM(AP)/SUM(AS)) 'ratio' ",1)

Error thrown:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 13. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... "+" ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... "+" ... "-" ...

Note that the following statement works:

Select A, SUM(AP)/SUM(AS) where B is not null GROUP BY A pivot B label SUM(AP)/SUM(AS) 'ratio'

The following statement does not work (testing to see if labelling the field has an effect):

Select A, IF(SUM(AP)/SUM(AS) > 1,1,SUM(AP)/SUM(AS)) where B is not null GROUP BY A pivot B

The sheet is here: https://docs.google.com/spreadsheets/d/1raQI22n3J08nKCHnz2LxRCXRP3dDnYJtGCEb8o4VUWY/edit#gid=1466522015

How to (Can I) execute a proper IF/THEN in the SELECT portion of a Google Sheets QUERY statement?

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

Try to process the if then before the query. See if this works

=ArrayFormula(QUERY({'JSON Data'!A1:B,  IF('JSON Data'!AP1:AP/'JSON Data'!AS1:AS > 1, 1, 'JSON Data'!AP1:AP/'JSON Data'!AS1:AS) } ,"Select Col1, sum(Col3) where Col3 is not null GROUP BY Col1 pivot Col2",1))