CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-query
Steve M
Steve M

Reputation: 33

How to Query a query

Using a query to select a col2 if col3 = criteria or select col3 if col4 meets the criteria.

I've tried multiple OR statements, tried adding a second query and nothing seems to work for me:

= Query(importrange(B7 ,"Sheet1!A1:G700"),"select Col2 Where Col3 = '"&Y116&"'", 0), 
Query(importrange(B7 ,"Sheet1!A1:G700"),"select Col3 Where Col4 = '"&Y116&"'", 0)

I want the result of col3 if the criteria is not matched in col2.

Upvotes: 2

Views: 1134

Answers (2)

TheMaster
TheMaster

Reputation: 50855

You can probably do this with a IF:

=ARRAYFORMULA(IF(index(importrange(B7 ,"Sheet1!A1:G700"),0,3)=Y116, 
    index(importrange(B7 ,"Sheet1!A1:G700"),0,2),
    index(importrange(B7 ,"Sheet1!A1:G700"),0,3)
))
  • If Col3 = Y116, return Col2,
  • else return Col3(You may add a another IF here to check if Col4=Y116)

Upvotes: 0

Joel Reid
Joel Reid

Reputation: 993

Google Visualization API Query Language does not allow joins, so you'll necessarily be performing multiple queries. But non-query formulas are sometimes not an option—they do not offer the flexibility and power of queries.

   I read your need as:
I need this if that, [inclusive] or this other if that other, but all in order.
   A generalized workaround could resemble:
List all the matches for the first select, plus a key column for sorting
Append all the matches for the second select, plus a key column for sorting
Sort the combination list by sorting key
Output just the data column

As a formula, using the row of each WHERE hit as the sorting key:

=ARRAY_CONSTRAIN(
  SORT({
    QUERY({sourceRange,ARRAYFORMULA(ROW(sourceRange))},
      "select Col2, Col5 where Col3 = 'that' ",0);
    QUERY({sourceRange,ARRAYFORMULA(ROW(sourceRange))},
      "select Col3, Col5 where Col4 like 'that other' ",0)
  },2,TRUE)
,99999,1)

Trying to blow it out for clarity while applying it to your formula:

=ARRAY_CONSTRAIN(
    SORT({
        QUERY({
                importrange(B7,"Sheet1!A1:G700"),
                ARRAYFORMULA(ROW(1:700)))
            },
            "select Col2, Col5 where Col3 = '"&Y116&"'",0
        );
        QUERY({
                importrange(B7,"Sheet1!A1:G700"),
                ARRAYFORMULA(ROW(1:700)))
            },
            "select Col3, Col5 where Col4 = '"&Y116&"'",0
        );
    },2,TRUE)
,700,1)

Which initially constructs an array of the form: 

Col2 entry where Col3 matched, row number
Col2 entry where Col3 matched, row number
Col2 entry where Col3 matched, row number
…
Col3 entry where Col4 matched, row number
Col3 entry where Col4 matched, row number
Col3 entry where Col4 matched, row number
…

which can then be sorted by the row-number, ascending: SORT( … ,2,TRUE)
and finally cut the keys and print the 700 rows, 1 column of data: ARRAY_CONSTRAIN( … ,700,1)

Upvotes: 1

Related Questions