Sheet specific QUERY unknown error cause

Question

I have this spreadsheet as an example to illustrate the problem.

I have two queries referencing different sheets on the same spreadsheet.

=QUERY(Transfers!A1:H, "select F where E = 'Mild Ale (24) '")

=QUERY(stocktake!A1:J, "select I where H = 'TOTAL:'")

One is displaying an error, the other is not. I'm unable to determine what is causing the error as both queries are sufficiently similar enough.

Much appreciated.

score 2 · Accepted Answer · answered Dec 04 '14 at 03:01

2

From Query Language Syntax:

All values of a column will have a data type that matches the column type, or a null value.

Your problem is that Column H has mixed types, number and string.

answered Dec 04 '14 at 03:01

Mogsdad

44,709
21
151
275

You could use one of the `LOOKUP` functions instead of a Query. – Mogsdad Dec 04 '14 at 12:28

score 1 · Answer 2 · answered Dec 04 '14 at 02:18

1

Seems to be because column H in stocktake has been formatted as automatic and the query is objecting to selecting a text value out of it.

One solution may be to apply numeric codes for TOTAL:, WASTAGE:, INCOME: and DISCREPANCY:. Another might be put those descriptions at the foot of ColumnB and adjust the query accordingly.
Another possibility would be to format ColumnH as Plain text.

answered Dec 04 '14 at 02:18

pnuts

58,317
11
87
139

Tried formatting to plain text, but no luck. This sheet is just a snippet of a very large, dynamically generated spreadsheet, so trying to avoiding re-writing a chunk of Apps Script. Thanks for the help – AshClarke Dec 04 '14 at 02:43

score 1 · Answer 3 · answered Dec 04 '14 at 10:44

In addition to previous post, you can convert everything to text in the query. Something like this should work:

=ArrayFormula(QUERY(stocktake!A1:J&"", "select Col9 where Col8 = 'TOTAL:'"))

If you want to output to be format as number, then adding +0 should do the trick:

=ArrayFormula(QUERY(stocktake!A1:J&"", "select Col9 where Col8 = 'TOTAL:'")+0)

NOTE: for the current situation an alternative (an probably easier) way, would be to use vlookup.

=vlookup("TOTAL:", stocktake!H1:I30, 2, 0)

Hope that helps ?

Sheet specific QUERY unknown error cause

3 Answers3