Google Query Language Concatenation

5
votes

Is it possible to concatenate the value from a column with a string in Google Query Language? I'd like to do something like this:

=QUERY('Business Income'!A1:E, "select 'Q' + quarter(A), sum(B) where A is not null group by 'Q' + quarter(A) label quarter(A) 'Quarter', sum(B) 'Income'")

Using data that looks like this:

Date          Amount
----------    -------
01/01/2015    XXXX.XX
02/01/2015    XXXX.XX
03/01/2015    XXXX.XX
04/01/2015    XXXX.XX
05/01/2015    XXXX.XX
...

And I'd expect this for the output:

Quarter    Income
-------    ---------
Q1         $XXXXX.XX
Q2         $XXXXX.XX         
Q3         $XXXXX.XX
Q4         $XXXXX.XX
2
google-sheetsgoogle-query-language
To my knowledge its not possible to concatenate fields with strings in Google Query language. I think the easiest solution would be to create an extra column where you calculate the quarter (=ceil(month(A)/3)) and prepend the 'Q'. Then you can use this column in the query. – jvdh
edit not possible anymore, but the ceil function doesn't exist, its actually called "ceiling" – jvdh
@pnuts I'll update the question. – LandonSchropp
@jvdh That's a bummer. :( A simple way to do concatenation in queries would have been nice. – LandonSchropp
FWIW I summarized the comments in an answer, so its easier to find for future visitors – jvdh

2 Answers

6
votes

Use format.

select quarter(A), sum(B)
where A is not null
group by quarter(A)
label quarter(A) 'Quarter', sum(B) 'Income'
format quarter(A) 'Q#'

Sorry for being late...

5
votes

It's not possible to concatenate fields with strings in Google Query language.

If you need the concatenated field for further data manipulations you could create an extra column where you calculate the quarter and prepend the Q (e.g. ="Q"&ceiling(month(A)/3)). Then you can use this column in the query.

Otherwise, if you just want to change the visual display you can format the number as "Q"# (as suggested by @pnuts).