Counting occurrences of text string in data using dataview (vote counting)

What I’m trying to do


I have been able to use dataview to output a table that displays data for ‘voter’ (before pipe symbol) and who they voted for (after pipe symbol), sorting for who was voted for.

What I am struggling to do, due to errors, is generate a table comprising two columns where the first gives all the people who received votes and the second column gives the number of votes they received. The query is in the same note as the data.

Example data set below:

vote:: Alice | Bob
vote:: Bob | Eve
vote:: Charlie | Bob
vote:: Dave | Alice
vote:: Eve | Dave

Things I have tried

I’ve tried many variations on the following code block:

table without ID
split(vote, " \| ")[0] AS Person, count(split(vote, " \| ")[1]) AS Votes
from [[this file]]
group by Person

Any help appreciated!

You mean something like the following:

WHERE file = this.file
FLATTEN vote as aVote
FLATTEN split(aVote, "\s*\|\s*") as Person
FLATTEN length(rows) as Votes
SORT Votes desc

Which based on the test data you produced gives:


The query picks all the votes from the current file, so you might want to change that if you want to pull votes from multiple files. It then continues to flatten (or split into its separate entities) the vote array, before using a split() which also eliminates a little whitespace around the | to split into each separate Person being voted for.

Lastly we group by the person, tally up the count and present the data sorted accorded to the number of votes.

Thanks that is what I have been trying to achieve (unsuccessfully!). The only slight issue is that the votes are all +1 more than they should be - what is the origin of the extra votes?

I can get round the issue by subtracting 1 from the length. I will close this off if someone has a more elegant solution!

How so? It seems correct according to your example data? Or how do you interpret each vote field?

Is the first part of the vote field the voter?

I assumed that each vote was a vote for two persons, as in anonymous voting processes. If the first part is the voter, you need to change the query a little. To allow for multiple votes by a voter like that, you could use something like:

FLATTEN slice(split(aVote, "\s*\|\s*"), 1) as Person

To replace the corresponding line in my original query.

Yes that was the issue - that change works perfectly. Many thanks!

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.