CODEWITHSUNDEEP
arraysgoogle-sheetspivotgoogle-sheets-formulagoogle-query-language
Solana_Station
Solana_Station

Reputation: 321

Query function unable to use array data range reference

Goal:

I want to optimize the data processing speed of the Query function, which is actually properly functioning, however there are times that it's extremely slow (1-2 minutes). So I'm in the process of improving my entire sheet. I referred to this: https://webapps.stackexchange.com/questions/106809/how-can-i-speed-up-google-sheets

With that said, I think I can improve the below Query setup by changing the data range from a regular reference to an array range reference.

How the current query works:

The below screenshot is a Query function that pulls data from a sheet (Blend Data, which also pulls data from another sheet) and counts the number of statuses (Technical Issue, In Consult, In Progress and Need Info) for each person (agents).

enter image description here

Here's what I have for the Query function.

=QUERY(
    'Blend Data'!$A$3:$C,
    "select C, COUNT(C) where B matches 
    'In Progress|In Consult|Need Info|Technical Issue'
    AND C matches'"
    &IFERROR(VLOOKUP(Values!F2,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F3,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F4,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F5,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F6,Values!F2:F,1,FALSE))&"'
    GROUP BY C pivot B",
    1)

Details about the Query setup:

More about the "Blend Data sheet":

The Blend Data sheet is using the Query function to pull 2 separate sheets ("Sheet A Data" sheet pulling data from a different Spreadsheet, "Sheet B Data" sheet pulling data from a different Spreadsheet) and I was able to do this with the Importrange function. Here's a screenshot.

enter image description here

Here's what I have for the Query function.

=QUERY(
    {
        IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs","'Sheet A Data'!$A$3:$H");
        IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc","'Sheet B Data'!$A$3:$H")
    },
    A1,
    1
)

Question:

As mentioned above, I want to change the data to an array format; however, I got an error (Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: C).

Here's what I tried to do.

=QUERY(
    {
        IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs","'Sheet A Data'!$A$3:$H");
        IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc","'Sheet B Data'!$A$3:$H")
    },
    "select C, COUNT(C) where B matches 'In Progress|In Consult|Need Info|Blocked By' AND C matches '"
    &IFERROR(VLOOKUP(Values!F2,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F3,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F4,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F5,Values!F2:F,1,FALSE))&"|"
    &IFERROR(VLOOKUP(Values!F6,Values!F2:F,1,FALSE))&"'
    GROUP BY C pivot B",
  1
)

Upvotes: 1

Views: 142

Answers (1)

player0
player0

Reputation: 1

when you use constructed range {} you cant use A,B,C references in query. instead, you need to use Col1,Col2,Col3 notation

=QUERY({
 IMPORTRANGE("1DeXTU7dJVJFYIHSP8U9dsKw3YNZVtEZIjbURAd54Xjs", "Sheet A Data!A3:H");
 IMPORTRANGE("1QUZ-ljkXNOvtnPg-a2FqM2PhYUdt_zX5gMLtB6GcFdc", "Sheet B Data!A3:H")},
 "select Col3,count(Col3) 
  where Col2 matches 'In Progress|In Consult|Need Info|Blocked By' 
    and Col3 matches '"
  &IFERROR(VLOOKUP(Values!F2, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F3, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F4, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F5, Values!F2:F, 1, 0))&"|"
  &IFERROR(VLOOKUP(Values!F6, Values!F2:F, 1, 0))&"'
  group by Col3 
  pivot Col2", 1)

Upvotes: 1

Related Questions