Dataview table - Combining different values in a single column

[willows]

What I’m trying to do

I’m struggling with a dataview table- I’d like to create a table which displays two (or more) values combined in a single column.

Things I have tried

l’ve searched a lot and attempted to implement some solutions similar to my case, but none have worked effectively. Seems like some people succeed doing something similar using dataviewjs or regrexreplace, however as a total beginner, it was a bit daunting to understand and apply them. I attempted to mimic few solutions, but encountered errors when dealing with ", ". Whenever I used that, dataview stopped functioning, pointing out that thing.

This is my file:

And this is the result of what I tried:

TABLE definition as Definition,meaning as Meaning__,synonyms as Synonyms,(reference + example) as Usage
FROM #voca 

and this the desired result:

I’m also curious if I can combine the file title, meaning and synonym together as well.
(for instance,
File. ________Defintion _____________Usage
test.
cognoscenti
전문가, 권위자

I prefer a version without those dots, but if it’s not feasible, please kindly advise on how to circumvent the result I got and make it look clean and organised. Any insights would be greatly appreciated

[holroy]

When you do example + reference the values are interpreted as strings, but you want to add the list together, so try flat(list(reference, example)) and see if that produces what you want.

[willows]

Despite I still see the bullets, your code solved my problem! It helped a lot, thank you.
By the way, I’d be so grateful if you, or anyone else, could answer some further questions!

So this is what I got:

TABLE flat(list(definition, synonyms,meaning)) as Definition,flat(list(reference, example)) as Usage
FROM #voca

1. Should I work with CSS to add a space/gap between definition and synonyms, synonyms and meaning, reference and example?

2. FROM #voca works for me, but I cannot get my results from a folder. Could you please help me?
   

TABLE flat(list(definition, synonyms,meaning)) as Definition,flat(list(reference, example)) as Usage
FROM “2023”

TABLE flat(list(definition, synonyms,meaning)) as Definition,flat(list(reference, example)) as Usage
FROM “2023/Nov”

These didn’t work. I changed “”into (), deleted them, etc, but it showed the same result, saying parsing failed.

3. When there’s no definition, meaning, reference, example or synonym in the original data file, it shows:
   

   
   
   these “-“s. How could I get rid of them?

4. I’d like dataview to search the files with all of definition, meaning, synonym, example, and reference.
   

   
   
   For example, this file contains meaning and example but missing definition and reference. I do not want the table to display this file, however I don’t want to delete definition::, reference:: and synonyms:: in the file.

I found

TABLE flat(list(definition, synonyms,meaning)) as Definition,flat(list(reference, example)) as Usage
FROM #vocabulary 
WHERE definition

returns me a table without the files which are missing values for definition. However I failed to apply this for multiple keys using + and ,. How could I solve it?

I’m struggling a lot and shall be soooo glad to have you help me! Have a great day!

[holroy]

@willows

Should I work with CSS to add a space/gap between definition and synonyms, synonyms and meaning, reference and example?

As long as these are bundled together without anything separating them from each other, you can’t target the various using CSS. You could possibly try to introduce something into the list, or format it another way instead of just concatenating the various lists.

@willows

FROM #voca works for me, but I cannot get my results from a folder. Could you please help me?

What’s the root folder of your vault for these files? Is it the 2023/Nov or is there something in front of the 2023 (within the vault folder structure). In any case, be sure to use straight quotes in the FROM. It should read FROM "2023/Nov", and not FROM “2023”.

@willows

When there’s no definition, meaning, reference, example or synonym in the original data file, it shows:

This could be countered by doing flat(nonnull(reference, example)) to remove any null entries from the combined list.

@willows

I’d like dataview to search the files with all of definition, meaning, synonym, example, and reference.

I’m not quite sure which files you want to exclude/include here, but if you wanted for both definition and meaning to have something in them, you could do something like:

WHERE length(definition) > 0 AND length(meaning) > 0

And if you do multiple WHERE it’s always an implied AND between, so the line above could also be written as:

WHERE length(defintion) > 0
WHERE length(meaning) > 0

If you want to include OR statements, as in you need to for either the definition or the meaning to be present, you could do something like:

WHERE (length(defintion) > 0 OR length(meaning) > 0 )

Hope that illustartes the two major ways to combine multiple criteria into the same WHERE-clause.

[willows]

At first, I didn’t understand your explanation on `FROM “2023”, but after a few hours, I somehow suddenly understood it! Problem resolved. I really appreciate it!

flat(nonnull(reference, example)) did not work, but I found out that it works when I use OR or AND so I just went with

TABLE flat(list(definition, synonyms, root, meaning)) as Definition,flat(list(reference, example)) as Usage
FROM "2023/Nov"
WHERE flat(nonnull(example))
WHERE flat(nonnull(reference))
WHERE flat(nonnull(definition))
WHERE flat(nonnull(root))

Despite the code working, I’m concerned this code just exclude files with null values in example, reference, definition, and root. I want those files to be displayed in my table but without bullets. I succeeded in getting rid of dashes by changing a dataview setting, so the only thing I need to remove now is the bullet.

Let me show you my file and table:

This is the table dataview displays for

TABLE flat(list(definition, synonyms, root, meaning)) as Definition,flat(list(reference, example)) as Usage
FROM "2023/Nov"
WHERE flat(nonnull(example)) OR flat(nonnull(reference))
WHERE flat(nonnull(definition))
WHERE flat(nonnull(synonyms))

I’ve excluded antonyms from the table, so please ignore them. As you can see, there’s nothing after reference::, and the table displays a single bullet for that value. I just want to get rid of a bullet, not the whole file from the table. Any ideas?

Also, although the code works, it often returns a ‘parsing failed’ message. If I erase most of the lines, exit source mode, and revisit the source mode, and write it again, it works. However, I’m concerned it might not work as smoothly when the size of my data grows bigger. Or is it unrelated to the size of the data it processes?

Unfortunately, WHERE length(definition) > 0 AND length(meaning) > 0 and its modification did not work. When I put those codes in dataview, it gives me this screen:

Oh now it works! Interesting. However, I’m not sure why this happens and how I can prevent it. Your previous response was sincerely helpful. I would wholeheartedly appreciate it if you could provide advice once more.

[holroy]

@willows

flat(nonnull(reference, example))

Oopsie, that was a typo it should be nonnull(flat(list(reference, example))). We combine into a list, then flatten it, and finally remove any null values. This should work. I think…