Dataview product comparison

What I’m trying to do

I’m trying to create a dataview table that will compare features of products. Features are listed in their own files with inline metadata. I need to do two things:

  • Display each feature as it’s own row, with each column representing a different product.
  • Partially match the metadata keys across all files so that I can match the features per row ( I want to be able to update the feature file without having to update the query every time I add a new feature category)

Ideally, the table would appear something like this:

Feature Key Product 1 Product 2 Product 3
featureCapacity 1TB 2TB 3TB

Things I have tried

I figured there might be some sort of regex matching for metadata keys, but I couldn’t seem to find anything.

I’ve managed to get some form of comparison with the following code, but it’s displaying each file as a row, and there doesn’t seem to be any way to transpose the data:

```dataview
TABLE WITHOUT ID
"**" + file.frontmatter.name + "**" AS "Plan",
feature AS "Feature"
FROM ""
WHERE contains(file.inlinks, this.file.link)
FLATTEN feature
SORT file.frontmatter.name ASC

Any help would be appreciated.

Could you show examples of the files having the features, and how you’d like the table til end up. We need more information.

1 Like

I gave a brief description in my initial post, but here’s a real-world example comparing hosting plans.

File Content

I have a file called KVM 1 with the following list:

  • feature::1vCPU Core
  • feature::4 GB RAM
  • feature:: 4 TB Bandwidth

and in a file called KVM 2

  • feature::2vCPU Core
  • feature::8 GB RAM
  • feature:: 8 TB Bandwidth
  • feature:: Priority Support

Dataview Output

I’d like the output dataview to look something like this:

KVM 1 KVM 2
1vCPU Core 2vCPU Core
4 GB RAM 8 GB Ram
4 TB Bandwidth 8 TB Bandwidth
Priority Support

Additional Note

I’d also like to change the lists from feature::2vCPU Core to something like featureCPU:: 2vCPU Core and select all features with something like feature*. This would make it easier to compare different products with varying features and ensure that the related features line up.

It’s also worth mentioning that it’s not always plans in the same product that I’m comparing, it’s often different products with similar features.

For staters, I can’t see easy ways to compare the correct parts of a single feature property. In essence it could then compare 1vCPU vs 8 TB Bandwidth. If you wanted to use just one property, you’d need to have something to identify which feature is related to which feature.

In your example this could be the last word of your feature, i.e. CPU, Bandwidth, RAM… But this would be prone for difficulties if you don’t have that identifying word. Queries could still be written, but they could easily break, I believe.

If you switched to using unique property makes, like featureCPU and similar, it would be easier to write the queries, but you’d still have a slight issue having the different products in the columns. That is, at least if you were using pure Dataview queries.

However, it can be done using dataviewjs, and javascript magic. Do you know any javascript coding?

I’m not able to write any queries tonight (it’s close to midnight in local time), but maybe I can get around to writing some query ideas tomorrow.

What mechanism will you use to specify which files to compare?

  • All files containing feature*?
  • All files is a specific directory?
  • All files in the current note associated with a tag/inline field like…
compare-list:: KVM 1
compare-list:: KVM 2
compare-list:: KVM 3

Brute force on feature

Here is an attempt at brute forcing based on the last word of the feature description. This should be possible to use directly on the two example files you gave, but I reckon you should have a better way of selecting which files to use somehow. The query uses the file names hard coded, and is more a proof of concept rather than a viable solution.

Base query

The basic idea is to flatten out the feature into its separate feature description, pick out the last word, and then group the results based on this last word. The base query then looks something like:

```dataview
TABLE rows.aFeature, rows.file.name 
WHERE folder = this.folder AND feature
FLATTEN feature as aFeature
FLATTEN slice(split(aFeature, " "), -1) as lastWord
GROUP BY lastWord
```
Explanation on base query

The first FLATTEN expands the list we’re working with by flattening the features listed in feature into a single field, aFeature, with one row per feature. If this doesn’t make sence, try the query without the last two lines , and use aFeature and file.name in the column lists.

The second FLATTEN does two operations. It splits the feature based on the space character into a list, so that 4 GB RAM becomes a list of 4, GB, RAM. The slice operation picks the last element, -1, of this list, and stores this word into lastWord.

Finally we do a GROUP BY lastWord which groups together rows where lastWord is equal. Now all previous fields are stored into the rows object. So aFeature becomes the list rows.aFeature, and so on.

If you run this in a folder holding the files KVM 1 and KVM 2 as example files you get this output:

image

As can be seen this lists the various features in separate rows, but it’s kind of hard to differentiate which feature belongs to which files, as you need to cross-reference two columns with their rows. Not good…

Filtering out per file

Using the previous query as the base input, we can manipulate the rows object to pick out a given file and display only that in a given column. One variant of this could then look like:

```dataview
TABLE KVM1, KVM2
WHERE folder = this.folder AND feature
FLATTEN feature as aFeature
FLATTEN slice(split(aFeature, " "), -1) as lastWord
GROUP BY lastWord as Feature
FLATTEN filter(rows, (r) => r.file.name = "KVM 1")[0].aFeature as KVM1
FLATTEN filter(rows, (r) => r.file.name = "KVM 2")[0].aFeature as KVM2
```
Explanation of the filtering

In this query we started with the base query, but after we’ve grouped the result, we’re going to filter out results related to a specific file. To do this we use the filter() command on the entire rows object, and for each element of that list, we pick one row (r) => , and checks the file name of that particular row of the list, through doing r.file.name = "KVM 1".

The filtering should give us a list of exactly one element, so we use [0] to pick the first (and supposedly only) element of that list, and furthermore we pick the aFeature element from this row. This is stored in a unique file name related field, so that we use that as a column in the first line of the query.

The effect of all this mumble-jumble is the following output:

image

An even uglier variant…

Here is a query which is getting hairy, where you can provide the filename in a list:

```dataview
TABLE File1 as first, File2 as second
WHERE folder = this.folder AND feature
FLATTEN flat(list(feature, file.name + " Filename")) as aFeature
FLATTEN slice(split(aFeature, " "), -1) as lastWord
GROUP BY lastWord as Feature

FLATTEN list(list("KVM 1", "KVM 2")) as filelist

FLATTEN regexreplace(filter(rows, (r) => r.file.name = filelist[0])[0].aFeature, " Filename$", "") as File1
FLATTEN regexreplace(filter(rows, (r) => r.file.name = filelist[1])[0].aFeature, " Filename$", "") as File2
SORT choice(contains(Feature, "Filename"), -1, Feature)
```

Which produces an output like:

Hopefully, you understand how you can expand upon this to compare more than two files, by extending the list, and by copy-pasting one of the latter FLATTEN lines, and replacing filelist[1] with filelist[2], and File2 with File3, and so on…

This query “extends” the feature list with the filenames, as we can’t have dynamic titles for the columns, AFAIK, and since we added this to the feature list, we also needs to do some extra cleanup to remove the Filename extension of that feature… And we also needs to extend the query with a little sorting hack to show the filename at the top. Not my cleanest query, but it gets the job done to some extent. :smiley:


I’ve not written the queries for the more specific field variants, (using featureCore or featureRAM and so forth), which most likely can be made dynamic. It’s a little more work to do those queries, and you’ve not responded whether that’s a feasible way forward for you, and whether you’ll be able to maintain such a query.

1 Like