CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
qinking126
qinking126

Reputation: 11885

used if statement in select query does not work

I have an order list in csv file. I want to transform the data.

here's my query. I have to add 2 new columns "gift box set" and blue oyser mushroom". If name in V column contains gift box set, then show 1, otherwise leave it blank. Please help me to correct my query. also how can I give a new column name for those 2 added columns?

=query(orders!A2:AP134, "select Q, K, V, if(V="Gift Box Set              5lbs", "1", ""), if(V="Blue Oyster Mushroom   3lbs", "1", "")", true)

enter image description here

Upvotes: 0

Views: 48

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA({Q2:Q, K2:K, 
 IF(V2:V="Gift Box Set              5lbs", "1", ), 
 IF(V2:V="Blue Oyster Mushroom   3lbs", "1", )})

or:

=ARRAYFORMULA(FILTER({Q2:Q, K2:K, 
 IF(V2:V="Gift Box Set              5lbs", "1", ), 
 IF(V2:V="Blue Oyster Mushroom   3lbs", "1", )}, 
 REGEXMATCH(V2:V, "Gift Box Set              5lbs|Blue Oyster Mushroom   3lbs"))

or:

=QUERY(INDEX(IF(REGEXMATCH(V2:V, "Gift Box Set              5lbs|Blue Oyster Mushroom   3lbs"), 
 {Q2:Q, K2:K, ROW(A2:A)^0}, )), 
 "where Col3=1", 0)

Upvotes: 1

kishkin
kishkin

Reputation: 5325

There is no if statement in QUERY syntax.

Try a simpler approach without QUERY:

=ARRAYFORMULA(
  {
    Q2:Q134,
    K2:K134,
    V2:V134,
    IF(V2:V134 = "Gift Box Set              5lbs", 1, ""),
    IF(V2:V134 = "Blue Oyster Mushroom   3lbs", 1, "")
  }
)

Upvotes: 1

Related Questions