7

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
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
LandonSchropp
  • 10,084
  • 22
  • 86
  • 149
  • 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 Nov 01 '15 at 12:55
  • edit not possible anymore, but the ceil function doesn't exist, its actually called "ceiling" – jvdh Nov 01 '15 at 13:02
  • @pnuts I'll update the question. – LandonSchropp Nov 02 '15 at 01:22
  • 1
    @jvdh That's a bummer. :( A simple way to do concatenation in queries would have been nice. – LandonSchropp Nov 02 '15 at 01:27
  • FWIW I summarized the comments in an answer, so its easier to find for future visitors – jvdh Nov 02 '15 at 12:09

2 Answers2

7

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

jvdh
  • 612
  • 1
  • 6
  • 19
7

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

masu
  • 1,919
  • 21
  • 25