CODEWITHSUNDEEP
google-sheets
lkesich
lkesich

Reputation: 11

Is there a way to cast data types in a Google sheets query() output?

I am working with a data set where records of a particular type have no value in the source data. Records of other types all have integer values for this field. For example:

Product Type ID
A 1 1234
B 2
C 1 5678
D 2

I am using the query function in Google sheets to return the actual ID for type 1 products, and a default ID stored as a named range for type 2 products.

={query(data!A2:C,"select A, '"&default_type_2_id&"' where B=2 label A 'product', '"&default_type_2_id&"' 'id'"); query(data!A2:C,"select A,C where B=1")}

Even though the named range refers to a single cell with a numeric data type, the query output returns it as a string.

Output w/ data type:

product id isnumber()
A 1234 TRUE
B 9999 FALSE
C 5678 TRUE
D 9999 FALSE

Changing the data type of the named range doesn’t affect the output. I’ve tried adding a format clause to query(), but it doesn’t coerce to numeric. Is there an equivalent of the SQL cast function for Google sheets query?

Here is a simplified example: https://docs.google.com/spreadsheets/d/1j-1OckdP4gUylfMRh6QtPB3cnD6ezWJITQxm6GdQb5o/edit#gid=192291414

I understand that there are solutions to the simplified example that don’t involve casting data types in the query function itself, e.g.

={data!A:B,arrayformula(if(data!B:B=2,default_type_2_id,data!C:C))}

or

={query(data!A2:C,"select A, 9999 where B=2 label A 'product', 9999 'id'"); query(data!A2:C,"select A,C where B=1")}

These won’t work in the real workbook for various reasons, so I’d like to figure out if I can alter the data type that query() returns.

Upvotes: 1

Views: 2474

Answers (1)

player0
player0

Reputation: 1

try forced conversion:

=INDEX(LAMBDA(x, IFERROR(x*1, x))({
 QUERY(data!A2:C, "select A,'"&default_type_2_id&"' 
  where B=2 
  label A'product','"&default_type_2_id&"''id'");
 QUERY(data!A2:C, "select A,C where B=1")}))

enter image description here

Upvotes: 1

Related Questions