Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
136 views
in Technique[技术] by (71.8m points)

Error in Google Spreadsheet Inner Queries

I have the following table in Google Spreadsheet. I want to select items from column B but based on a condition that only these rows are selected where the values of columns A and D match.

enter image description here

I constructed the following query to check that a syntax is correct:

=CONCATENATE("Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"));".*'")

Executing this gives the following result:

Select B where A matches '.*Item1.*|.*Item2.*|.*Item3.*'

When I now use this syntax in a query, then the result is correct: 1 2 3

=query(A2:B; "Select B where A matches '.*Item1.*|.*Item2.*|.*Item3.*'")

But when putting all this together I got the following error message. Why is that?

=query(A2:B; "Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"));".*'")

enter image description here

question from:https://stackoverflow.com/questions/65598780/error-in-google-spreadsheet-inner-queries

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Solution:

You put an ; instead of & at the end:

=query(A2:B; "Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"))&".*'")

Explanation:

This ".*'" was passed as a third argument. But the third argument (which is optional) needs to be of type number.

See the official documentation for more details:

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...