Updating a query for false positives-CodePudding

More of a "point me to the right resources" question, but here we go...

I work in a compliance role at a very small start-up and review a lot of, let's say, bank transfers/direct deposits/ACHs every day. About 3K-5K give or take. A report is pulled from BigQuery that I then connect to Google Sheets.

My question is there's a lot of false positives (basically, "posting data" that repeats often). I'm trying to think of a way to eliminate it.

One idea was just to update the query for key words:

WHERE postingdata LIKE 'PersonName%'

But this is tiring and time-consuming. And I feel like there's got to be a better way, perhaps 'filtering' the results and then feeding it back to the query. Any ideas or tips or just general thoughts?

CodePudding user response：

within GS you can try UNIQUE or QUERY with group by aggregation or SORTN with mode 2 as 3rd parameter

CodePudding user response：

In this case you can use

In this case, I will use the group by clause.

SELECT account,TypeTransaction,amount,currency
FROM `tblBankTransaction`
group by account,TypeTransaction,amount,currency

And it returns this data:

You can see in this example that the account 894526972455 with a deposit only returns 1 row. The same account returns a second row, but is a transfer; it’s a different type of transaction. It depends on the information you have and what column you want to group.