CODEWITHSUNDEEP
google-sheets
Adiansyah
Adiansyah

Reputation: 353

Google Sheets Query Coalesce?

is there any query syntax that woks like coalesce in google sheets?

if i have a source like pict below

enter image description here

the result i want is only getting id and time if status is true, but the time is only exist in one col either in check column or report column

so the result would be like this...

enter image description here

I tired this but doesn't work

=QUERY(A1:D4, "SELECT A, COALESCE(B, C) WHERE D = TRUE")

any ideas or workarounds? Thanks

Upvotes: 4

Views: 15637

Answers (5)

aberson
aberson

Reputation: 121

you can use something like this:

=QUERY(transpose(B1:H1),"Select Col1 where Col1 is not null limit 1",0)

This transposes the row into a column, queries all non-null values from that column, and then set limit 1 to return the first value. So essentially you are selecting the leftmost non-empty value from your row.

I can't take full credit for this, I must have gotten it somewhere else... but it's in one of my sheets.

Upvotes: 4

Bprime
Bprime

Reputation: 1

Add a column after Status call it Time (column E), whereas each formula follows this format (assuming your table starts at A3:E)

=if(A4="","",if(B4<>"",B4,C4))

Now query A3:E like so,

=query(A3:E,"Select A,E where D=TRUE")

Upvotes: 0

Argyll
Argyll

Reputation: 9869

You can take advantage of the either or situation and concatenate the 2 columns.

=filter({A2:A,concat(B2:B,C2:C)},D2:D)

Also see local array and filter

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34440

A very short one just for the special case of 2 columns where you know that only one of them is populated and they are dates:

=ArrayFormula(to_date(if(D2:D,B2:B+C2:C,)))

enter image description here

Maybe the simplest formula which behaves like coalesce would be

=iferror(if(D2,hlookup(9^9,B2:C2,1,true),))

It's just a pull-down formula but will pick up the first non-blank column from a range of columns containing numbers or dates. If the columns are all blank, it returns blank.

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(SPLIT(FLATTEN(QUERY(TRANSPOSE(
 ARRAY_CONSTRAIN(IF(D2:D=TRUE, {A2:A, IF(B2:C="",,"×"&B2:C), D2:D}, ), 9^9, 
 COLUMNS(A:C))),, 9^9)), "×")))

Upvotes: 2

Related Questions