Dataview table that outputs outgoing links by first letter

What I’m trying to do

I’m trying to use dataview to output a table with 5 columns: file link, lines marked with metadata “mindsum”, outgoing links starting with ~, outgoing links starting with %, outgoing links starting with $.

So it would look like this:

File Mindsum ~Outlinks %Outlinks $Outlinks
Note1 (LIST) Lines a, b, c (LIST) ~link1, ~link2 (LIST) %link1 (LIST) $link1, $link2
Note2 (LIST) Lines a, b (LIST) ~link1, ~link2, ~link3 (LIST) %link1, %link2 (LIST) $link1
Note3 (LIST) Lines a (LIST) ~link1 (LIST) %link1, %link2 (LIST) $link1, $link2

I currently have a query to output all the outlinks in one column:

TABLE mindsum, file.outlinks
FROM [[]]
WHERE mindsum

I‘m struggling with breaking up the outlinks into different columns.

(A second approach I’d be open to: Having the single column of outlinks sorted by first letter, instead of divided into different columns.)

Any suggestions would be incredibly helpful – thank you in advance!

Things I have tried

In my searches, I came across FILTER, startswith, and WHERE example[0] = but I can’t seem to figure out the correct way to incorporate them into the query.

Apologies in advance for twitch-worthy syntax, and if using special characters in the filename is the big no-no – it’s my first rodeo.


	filter(file.outlinks, (l) => l.startsWith("~")) as ~Outlinks,
	filter(file.outlinks, (l) => l.startsWith("%")) as %Outlinks,
	filter(file.outlinks, (l) => l.startsWith("$")) as $Outlinks,
	FROM [[]]
	WHERE mindsum

… gives me an error message saying “Cannot call type ‘null’ as a function”

Where first letter

	mindsum, file.outlinks as "~Outlinks", file.outlinks as "%Outlinks", file.outlinks as "$Outlinks"
	FROM [[]]
	WHERE mindsum
	WHERE "~Outlinks"[0] = "~"
	WHERE "%Outlinks"[0] = "%"
	WHERE "$Outlinks"[0] = "$"

… outputs all outlinks in all outlink columns (i.e. not just those that start with ~ or % or $).

You’re close, but not made the entire way to the finish line, here are some comments to help you understand the query below:

  • In DQL the functions which are defined are used in front of the variable, so not l.startsWith, but startswith(l)
  • A link is a compound object and within DQL you’ll need to use meta(link) to get the sub fields of the link named link. Then you’ll have access to display, embed, path, subpath and type

So here is a whimsical query which should work in your case as well listing 10 files with outlinks (but less than 4 of them) splitting them up into columns related to the letter F:

  map(file.outlinks, (ol) => lower(meta(ol).display)[0]) as "First letter", 
  map(file.outlinks, (ol) => lower(meta(ol).display)[0] < "f") as "First letter < f", 
  filter(file.outlinks, (ol) => lower(meta(ol).display)[0] < "f") as "Before F", 
  filter(file.outlinks, (ol) => startswith(lower(meta(ol).display), "f")) as "F", 
  filter(file.outlinks, (ol) => lower(meta(ol).display)[0] > "f") as "After F"

WHERE length(file.outlinks) > 0 
  AND length(file.outlinks) < 4

So to get to the file name of the links, I’ve used meta(ol).display which I lowercased in this example. This is then used to do the various filter() operations. The second and third columns shows what the first letter of each outlink is, and whether it’s truthful that this letter is less than f.

The untested, but hopefully working query for you should look something like:

  filter(file.outlinks, (ol) => startswith(meta(ol).display), "~") as "~Outlinks",
  filter(file.outlinks, (ol) => startswith(meta(ol).display), "%") as "%Outlinks",
  filter(file.outlinks, (ol) => startswith(meta(ol).display), "$") as "$Outlinks"
FROM [[]]
WHERE mindsum

Updated again: Corrected some typos related to parentheses

Hi holroy, thank you so much!

Your explanation was incredibly helpful in clarifying where my query was stalling, and also in pointing me in new directions. I’m seeing a lot of other ways that meta(link) will be useful, so I’ll be doing more reading on this.

I initially got a parsing error when I used the query you suggested. I figured out that startswith needs to be followed by 2 parentheses (( not just 1, since this query didn’t include lower( . Now it works perfectly!

Do I mark your reply as the Solution, or will that stop you from making that correction?

Again, thank you :smiley:

When you mark it as a solution it’ll still be open for editing in seven days.

I realize my description of the typo wasn’t very clear. Here’s the query that worked for me:

  filter(file.outlinks, (ol) => startswith((meta(ol).display), "~")) as "~Outlinks",
  filter(file.outlinks, (ol) => startswith((meta(ol).display), "%")) as "%Outlinks",
  filter(file.outlinks, (ol) => startswith((meta(ol).display), "$")) as "$Outlinks"
FROM [[]]
WHERE mindsum

Sorry for those typos, and thanks for the notification. I’ve now updated the solution post to have working queries.

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