Let’s Use Spreadsheet Query Function (9) line breaks in the data etc. Query function error countermeasures

クエリイメージ Spreadsheet
この記事は約11分で読めます。
Advertisements

Target Readers

  • who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
  • who are extracting data with vlookup, but who feel the limit
  • who want to analyze data
  • Even non-programmers who want to use queries
  • who are asking queries delicious? (meaning that the query is delicious)

Query function series planned (planned)

  1. Outline and purpose
  2. Syntax of query function
  3. Basic SELECT clause (get all, get only specific columns)
  4. Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
  5. Try using the GROUP BY clause
  6. Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Sorting basics ORDER BY clause 
  8. Required if you do SQL Try a subquery
  9. There are line breaks in the data. contains/starts with helps when you can’t handle like ← this time

Examples of unexpected behavior that often occurs in the Query function (query function)

So far, we’ve seen how to use query functions. I studied various things such as WHERE and Group BY. There was also processing of subqueries and data binding systems. If you have a function you have forgotten, please review the past episodes.

This time, the coat color is a little different, but it is a countermeasure when the query function that occurs when hitting in practice does not behave as imagined.

Conclusion Most non-image behavior is that the data structure is adversely affected and the data is blank. Let’s look at three common examples.

Query function:Common error list

  1. There are line breaks in the data and it is not searched well
  2. Only one column data is different and it is not searched well
  3. The cells are merged

When there is a line break in the data and the countermeasure

Now, suppose you have this kind of data. Now, suppose you want to find TOM data.

Data that the Query function is difficult to handle

Now, suppose you want to find TOM data. If you search for TOM by name, it should come out.

=query(data range, "select * where A = 'TOM'")

Oh, nothing is returned, right? Isn’t it?

the result of =query(data range, “select * where A = ‘TOM'”)

I wonder why? To check the cause once, it is quick to display all. Then, it’s strange, isn’t there TOM?

search by select *

Please wait a moment, if you look at the TOM data, you can see that the name column has a line break and aaa is included. Oh yeah, it wasn’t just TOM, so why not use Like to search by prefix match?

=query(data range,"select * where A like 'TOM%'")

Actually, even if I do this, nothing is returned.

The problem isn’t whether it’s a match or like, it’s because it contains line breaks.

Data that contains line breaks is contains or starts with / ends with

If line breaks are included, there is a way to symbolize the line breaks with CHAR, but the easier way is contains. If TOM is included, it will be searched.

If you search by contains, even if line breaks are included, it can be handled as long as the data specified by contains is included. Also, the same thing can be done with starts with, even if it is confirmed that the name starts with TOM.

Attention points for using contains/starts with

As a point to keep in mind when using contains, if the name is A “TOM” or if a person like “TOM” THON is also included in the data, it will be extracted. Contains is useful, but keep in mind that some of them don’t match cleanly.

Then, as for starts with, A “TOM” is not included in starts with, but “TOM” THON is included.

When using contains or starts with, it is good if the data has a special mark (symbol such as ★ or !) To identify the data.

However, it is recommended that you do not create such data in the first place and set a special flag by separating columns for each status. Dirty data is an enemy to the query function.

Only one column data is different and it is not searched well

Now, let’s look at the same data as before.

Now try to search by age. How old was Ben?

=query(data range,"select * where A = 'Ben'")

Oh, Ben’s age is blank. ..

Looking at the original, Ben’s data is actually aaa, and although all the other age columns are numerical values, only Ben has a character string.

Then, I think some people might think that aaa is displayed. However, this is the specification of the query function, and there are the following.

QUERY will convert columns with mixed data types into one data type. If the data is mostly numerical values, then text strings (eg 1-2 in your data) will be converted to blank cells.

When multiple data types are mixed, a small number of data types will be blank cells. Is it a bug? It is a specification that makes me think, but it is the current specification.

In this case as well, only Ben’s age was a letter, and the others were numbers, so Ben’s age was Blank.

Countermesure

As a countermeasure when multiple data types are included, it is a painstaking measure, but if all are strings. ..

The related article will be this stackoverflow article. Sounds pretty annoying.

Google sheet Query Function Returns blank result from cell with data
I am creating a spreadsheet for my work place that will find drivers for the LED's we use but I've run into a slight problem. i pull in the data from another sh...

So, in reality, it’s better to put validation rules in the data first so that you can’t enter data other than numbers, rather than doing that. Since you can select numbers, texts, and dates, it basically works with what you want to support with the query function.

The cells are merged

It’s annoying, but it’s often done. .. What happens when you merge cells is that the data other than the first cell will be blank. This is the same specification for GAS, isn’t it?

Actually, there is no good way to deal with this.

It is a cover in operation. Cells do not merge. Rather, keeping the data normalized is the best solution.

SUMMARY

Now, I’ve looked at Query functions nine times. It was long.

For small amounts of data, sumifs and vlookup are sufficient, but as the data grows, it often becomes heavier when processed by multiple functions, right? Since the query function is a single function process, I feel that it will not be heavy on the skin (although I have only tried it with data up to about 1 million rows).

Query functions are easy to get started as part of your study of SQL, and I hope you will get used to them. And let’s open the door to the actual SQL. You will notice that there are similar ones.

Series: Let’s Use Spreadsheet Query Function

While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.

  1. Let’s Use Spreadsheet Query Function (1) Outline and Purpose
  2. Let’s Use Spreadsheet Query Function (2) Structure of query function
  3. Let’s Use Spreadsheet Query Function (3) SELECT Clause
  4. Let’s Use Spreadsheet Query Function (4) WHERE Clause
  5. Let’s Use Spreadsheet Query Function (5) Group by
  6. Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Let’s Use Spreadsheet Query Function (7) Sorting basics “ORDER BY” clause
  8. Let’s Use Spreadsheet Query Function (8)Required if you do SQL!! Try a subquery with the Query function
  9. Let’s Use Spreadsheet Query Function (9)line breaks in the data etc. Query function error countermeasures

コメント