Suppose I have a notes that are tagged with #entertainment which contain the fields movie and show. I would like to be able to have a dataview query that combines these fields into a single column, presented as a list without indentations.
Things I have tried
An online search yielded a solution that sort of works:
TABLE (movie + ", " + show) as “Watched”
FROM #entertainment
This succeeds in concatenating the movie and show fields, but it presents the items as a single line of text separated by commas rather than as a list.
Looking at the dataview documentation, I tried this as an alternative:
TABLE list(movie, show) as “Watched”
FROM #entertainment
This returns a single list, but it is a nested list. For instance, if I have the fields movie:: abc, movie:: def, show:: xyz then it returns a list as
abc
def
xyz
This is close to what I want, but I’d like to remove the nesting so that it’s all presented in a single list like
After digging into documentation more and playing around a bit, I found a way to hack something together that works:
TABLE split(join(list(movie, show)), ", ") as “Watched”
FROM #entertainment
In this query,
list(movie, show) creates a list of lists
join(list(movie, show)) converts the list of lists into a single string where each entry in list(movie, show) is separated by the string ", "
split(join(list(movie, show)), ", ") converts that string into a single list
So, that works, but it also feels like a bit of a clunky workaround. For my own knowledge, is there a more elegant in-built way to do this sort of thing?
edit: in particular, what would be handy would be a function that just appends items to a list, or concatenates lists. e.g. if list1 = [a, b] and list2 = [c, d], some function F where F(list1, list2) would return [a, b, c, d]. I don’t see anything like this in the dataview documentation. The closest thing is the list function, but when given lists as input it creates lists of lists instead of a single concatenated list. e.g. list(list1, list2) gives [ [a, b], [c, d] ]
You could do the split on your first example query also, which would get rid of the list and join.
I do not see any functions in the DQL list that do list concatenation.
Do you mean just using split(movie, show) ? That doesn’t seem to work.
I should also add that my above solution produces a null entry in the list if one of the fields for a given note is empty. e.g. if a note has show:: abc but no movie field, it produces a list in the table like
abc
-
To get around this behavior I had to add yet another function to filter out the null entries, and it’s starting to get ugly:
TABLE filter(split(join(list(movie, show)), ", "), (x) => x != “\-”) as “Watched”
FROM #entertainment
Thanks, that does help to streamline things a little. So now what I have is
TABLE filter(split((movie + ", " + show), ", "), (x) => x != “-”) as “Watched”
FROM #entertainment
Still, it seems like there should be a more elegant solution, but maybe that would require an update to dataview itself to provide functionality for flexible field combination, or nested fields, or something like that.