2

How can I (or is it possible?) use something like CASE statement in T-SQL inside QUERY in Google spreadsheet?

I want to do something like this:

=QUERY(A6:AI,"select A,(CASE WHEN D>2.5 THEN 'Yes' ELSE 'No' END),E,C,J")

I want basically a custom column with Yes/No values based on other column. Is it possible?

EDIT: To better explain, I have data in table Existing table and I would like to transform it to the Transformed table using QUERY statement:

enter image description here

So I need something to say: if column D is empty, print No, otherwise print Yes. This has to be in the QUERY because it's not the last column, there will be more data after column Finished. So I have this:

=QUERY(A4:D,"Select A, B, (CASE WHEN D='' THEN 'No' ELSE 'Yes' END)") - But that doesn't work

Thank you for help,

player0
  • 124,011
  • 12
  • 67
  • 124
Adam Wojnar
  • 473
  • 6
  • 19

1 Answers1

2

CASE & THEN are not supported in google's query language

try:

=INDEX({A6:A, IF(D6:D>2.5, "yes", "no"), E6:E, C6:C, J6:J})
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, I'm not sure how can I use it in the QUERY though. Can please help me out? – Adam Wojnar Jan 12 '22 at 22:18
  • @AdamWojnar can you share a copy/sample of your sheet? – player0 Jan 12 '22 at 22:29
  • Please take a look at my edited question – Adam Wojnar Jan 12 '22 at 22:33
  • The spreadsheet is only like 15% complete, so It's hard for me to show you the whole picture. But it's actually a simple transformation. Basically some columns will be copy&pasted as they are, some will be modified to show "Yes/No" information. If it's possible without `QUERY` then I'm all for it. Probably when I wrote *"This has to be in the QUERY"* - was a little bit too strong. I thought I don't have other options – Adam Wojnar Jan 12 '22 at 23:43
  • 1
    @AdamWojnar in that case all you need is included in provided answer. if range does not change use the range itself (either 1 column A6:A or multiple columns A6:C) and if it changes to yes/no use IF statement to change it as you wish. other than that... all is comma-separated inside {} array brackets and wrapped into INDEX or ARRAYFORMULA – player0 Jan 13 '22 at 01:07