CODEWITHSUNDEEP
google-sheetsgoogle-query-language
LandonSchropp
LandonSchropp

Reputation: 10244

Google Query Language Concatenation

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

Upvotes: 7

Views: 10083

Answers (2)

masu
masu

Reputation: 1991

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...

Upvotes: 7

jvdh
jvdh

Reputation: 612

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).

Upvotes: 7

Related Questions