If query is empty return "no data"

Question

I'm using Google Sheets and my database looks like this:

I have a query formula that changes dynamically based on the month:

my query:

=QUERY('database',"SELECT B,C WHERE A = '"&A1&"'")

At the beginning of the months I don't have data, so I want to display something like this:

I want the column userID permanent in all month, but the column name to display "NO DATA"

I already tried with IFNA or IFERROR but that won't work, because if there's no data, the query is still successful because it displays the column names.

NightEye · Accepted Answer · 2022-03-16 05:13:13Z

1

You can try this longer combination of several formulas:

=arrayformula( query({A2:C;split(E1&"_"&unique(filter(B2:B,B2:B<>""))&"_NO DATA", "_")}, "select Col2, max(Col3) where Col1 = '"&E1&"' group by Col2 order by Col2 label max(Col3) ''", 0) )

Where:

A2:C is the dictionary
B2:B is the column of userIDs
E1 is the Month

Outputs:

edited Mar 16, 2022 at 5:13

answered Mar 16, 2022 at 4:51

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

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

3 Comments

David Salomon Over a year ago

thanks for the solution, for some reason I get this error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

David Salomon Over a year ago

my modified formula =arrayformula( query({Data!$A$2:$C;split(G3&""&unique(filter(Data!$C$2:$C300,Data!$C$2:$C300<>""))&"_NO DATA", "")}, "select Col2, max(Col3) where Col1 = '"&G3&"' group by Col2 order by Col2 label max(Col3) ''", 0) )

NightEye Over a year ago

Hi @DavidSalomon, in your case, you missed out 2 things. First, you are missing the underscore after G3 and for split after _NO DATA, that's why you are missing values on some rows. Second, you are filtering name, not userID. you should get the unique values of filtered userIDs. Try this:

=arrayformula( query({Data!$A$2:$C;split(G3&"_"&unique(filter(Data!$B$2:$B300,Data!$B$2:$B300<>""))&"_NO DATA", "_")}, "select Col2, max(Col3) where Col1 = '"&G3&"' group by Col2 order by Col2 label max(Col3) ''", 0) )

Collectives™ on Stack Overflow

If query is empty return "no data"

1 Answer 1

Where:

Outputs:

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Where:

Outputs:

3 Comments

Related