Help with Compound Dataview Values

Things I have tried

I’ve searched through tons of snippets but am still having trouble dealing with key with multiple values (separated by " | " ). These compound values make Obsidian super-useful so I’d really like to figure out syntax.

What I’m trying to do

I have a page with the following:

Monitoring:: FundA | Do This
Monitoring:: FundA | Do That
Monitoring:: FundB | Do Something Else

I’d like to be able to see a result with only “Fund A”.

image

The code I’m using is:

TABLE WITHOUT ID 
	split(Monitoring, "\|")[0] AS "Fund", 
	split(Monitoring, "\| ")[1] AS "Action Item"
FROM #MonitoringFunds 
WHERE Monitoring
FLATTEN Monitoring

I’ve tried using the WHERE argument to see only FundA, but to no avail. In addition, I’ve tried using GROUP BY to see FundA listed only once, but also to no avail.

Thank you from a newbie !!

1 Like

Very interesting! Compound queries definitely seem useful.

There is likely a much better way, but the closest I could come up with is to build individual queries for the funds. The only problem is that if a note has at least one Monitoring value that begins with FundA | then the table will return the values for the other funds as well. But since they are sorted, it makes it a little better. There is probably a way to make this work. Hopefully this example query for “FundA” helps you or someone figure out the solution:

TABLE WITHOUT ID
	split(Monitoring, "\|")[0] AS "Fund", 
	split(Monitoring, "\| ")[1] AS "Action Item"
FROM #MonitoringFunds
WHERE filter(Monitoring, (t) => regexmatch("FundA \| .*", t))
FLATTEN Monitoring
SORT Monitoring ASC

Good luck!

2 Likes

One of the things that FLATTEN does is undo a layer of nesting of a list, such as the output of split(Monitoring, " \| "). Note that you probably want spaces on both sides of your separator in that split since "FundA<space>" and "FundA" are not equal.

So changing the last line of your starting code:

FLATTEN split(Monitoring, " \| ") AS "Monitored"
WHERE contains(Monitored[0], "FundA")

Then you can change the column definitions in the top of your query to

    Monitored[0] AS "Fund",
    Monitored[1] AS "Action Item"

Note that I just made up the name “Monitored” for what to call the result of the split operation - you can call it whatever, just change it everywhere it is used.

Does this allow the “WHERE” to work?

Another note: if you use GROUP BY you’ll want to get rid of the “without id” on your table to actually see the funds, at least while you are trying to make sure everything is working!

Good luck!

2 Likes

Input

Summary

dictionary files

  • filename : dic_20220303
---
Date: 2022-03-03
---
Monitoring:: FundA | Do This0303
Monitoring:: FundA | Do That0303
Monitoring:: FundB | Do Something Else0303

  • filename : dic_20220308
---
Date: 2022-03-08
---
Monitoring:: FundM | Do This0308
Monitoring:: FundM | Do That0308
Monitoring:: FundN | Do Something Else0308

  • filename : dic_20220403
---
Date: 2022-04-03
---
Monitoring:: FundQ | Do This0403
Monitoring:: FundQ | Do That0403
Monitoring:: FundP | Do Something Else0403

  • filename : dic_20220408
---
Date: 2022-04-08
---
Monitoring:: FundR | Do This0408
Monitoring:: FundR | Do That0408
Monitoring:: FundS | Do Something Else0408

  • filename : dic_20220503
---
Date: 2022-05-03
---
Monitoring:: FundK | Do This0503

  • filename : dic_20220508
---
Date: 2022-05-08
---
Monitoring:: FundJ | Do Something Else0508


DQL10_split_flatten : For multiple Monitoring field(s) per file

Summary

Main DQL

Code Name Data type Purposes Remark
DQL10_split_flatten a string or a list 1.To get s_page.Fund_DQL and s_page.ActionItem_DQL from s_page.Monitoring

Code DQL10_split_flatten

````ad-info
title: DQL10_split_flatten =>For multiple Monitoring field(s) per file
collapse: close
icon:
color:

TABLE WITHOUT ID 
      file.link AS "File",
      Monitoring AS "Monitoring",
      split(Monitoring, "\s+\|\s+")[0] AS "Fund", 
	  split(Monitoring, "\s+\|\s+")[1] AS "Action Item"      
FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
WHERE (  Monitoring != null )          
SORT file.name ASC
FLATTEN Monitoring

````

Inline DQL

aMonitoring=[“FundA | Do This”, “FundA | Do That”]
a_string_splited==split("FundA | Do This", "\s+\|\s+") //=>FundA, Do This

/\s+\|\s+/ ==>The spaces beside the "|" will be found and removed with "|" by `split`.

Screenshots(DQL10)


DQL20_split_flatten_filter : For multiple Monitoring field(s) per file

Summary

Main DQL

Code Name Data type Purposes Remark
DQL20_split_flatten_filter a string or a list 1.To filter by Monitoring
2.To exclude the display of some field contents

Code DQL20_split_flatten_filter: final

````ad-info
title: DQL20_split_flatten_filter =>For multiple Monitoring field(s) per file 1.To filter by Monitoring 2.To exclude the display of some field contents
collapse: close
icon:
color:

TABLE WITHOUT ID 
      choice(
          contains(Monitoring, "FundQ |"),
          split(Monitoring, "\s+\|\s+")[0], 
          ""
       ) as "Fund2",
      choice(
          contains(Monitoring, "FundQ |"),
          split(Monitoring, "\s+\|\s+")[1],
          ""
      ) as "ActionItem2"   
   
FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
WHERE ( Monitoring != null )
    AND ( contains(Monitoring, "FundQ |") )
SORT file.name ASC
FLATTEN Monitoring

````

Screenshots(DQL20)


DVJS30_flatten_filter

Summary

3.4.1. Main DVJS

Code Name Data type Purposes Remark
DVJS30_flatten_filter a string or a list 1.To flatten
2.To filter by dictionary.Fund

3.4.2. Code DVJS30_flatten_filter

Summary_Code
title: DVJS30_flatten_filter =>To break up multiple group Dataview inline fields in the file into individual groups
collapse: close
icon: 
color: 
```dataviewjs
// M11. Let's gather all relevant pages: M11, M15
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"')
    .where((page) => page.Monitoring !== null);
    

// M23. Now let's transform from thing per Monitoring
// #####################################################################
let dictionaries = [];

for (let page of pages) {
    // M23.IF01: Classify '[object Object]' into '[object Array]' and '[object notArray]'
    // #####################################################################
    let sObjectType = "";
    if (dv.isArray(page.Monitoring)) {
        sObjectType = "[object Array]";
    } else {
        sObjectType = "[object notArray]";
    }

    // M23.IF02: Define page.Schedul an array + Redefine sObjectType
    // #####################################################################
    if (sObjectType === "[object notArray]") {
        page.Monitoring = [page.Monitoring];
        sObjectType = "[object Array]";
    }


    // M23.EL03: Transform the data when page.Monitoring is an array
    // #####################################################################
    for (let i = 0; i < page.Monitoring.length; i++) {
        dictionaries.push({
            file: page.file,
            date: page.date,
            Monitoring: page.Monitoring[i],
            Fund: page.Monitoring[i].replace(/\s+\|\s+.+$/, ""),
            ActionItem: page.Monitoring[i].replace(/^.+\s+\|\s+/, ""),
        });
    }
}


// M24. dictionaries: JS arrays to Data Arrays to be sorted
// #####################################################################
dictionaries = dv.array(dictionaries);


// M25. sort it by Fund in ascending order: 
// M31. Let's  filter it by dictionary.X : M31,M33
// #####################################################################
dictionaries = dictionaries
    .sort((dictionary) => dictionary.Fund, 'asc')
    .filter(
        (dictionary) =>
            dv.func.contains(dictionary.Fund, "FundQ")
    );


// M41. And let's print a table of the dictionaries
// #####################################################################
dv.header(5, "M41. Print a table of the `dictionaries`");
dv.table(
    ["N", "File", "Monitoring", "Fund", "Action Item"],
    dictionaries.map((dictionary, index) => [
        index + 1,
        dictionary.file.link,
        dictionary.Monitoring,
        dictionary.Fund,
        dictionary.ActionItem,
    ])
);

```

3.5. Screenshots(DVJS30)


4. Explanation: DVJS30_flatten_filter

Summary_Explanation

4.1. Overview

4.1.1. Main variables

  • The following table presents the main variables in the Code Main.
Variables Data type Lifetime Declared by Remark
pages Array When the code is completed let Array of Hashes
page Hash In the {} curly brackets
dictionaries Array When the code is completed let Array of Hashes
dictionary Hash In the {} curly brackets
4.1.1.1. The data structure of the pages
  • The pages array consists of each page hash.
  • The page is pages[i], where i=0, …, pages.length-1.
4.1.1.2. The data structure of the page
  • Take the dic_20220303 file for example.
  • filename : dic_20220303
---
Date: 2022-03-03
---

Monitoring:: FundA | Do This0303
Monitoring:: FundA | Do That0303
Monitoring:: FundB | Do Something Else0303


  • The plain page hash from a file is created by the dv.pages() function and consists of three hashes : the page.file hash, the YAML fields and the Dataview inline fields.
  • Here is a slice of the page hash, where page.file.name is dic_20120919.
  • At Step M23, the following hash is transformed into two hashes, which are stored in the dictionaries array.
{
  file: {
    name: 'dic_20220303',
    ctime: '2022-03-03T19:30:50.586+08:00',
  },
  date: '2022-03-03T00:00:00.000+08:00',
  Monitoring: ['FundA | Do This0303', 'FundA | Do That0303', 'FundB | Do Something Else0303'],
}

4.2. Step M11: Sources

  • 【M11】: Suppose that there are many files located in the path 100_Project/02_dataview/Q06_Monitoring/Q06_test_data .

  • 【M15】: Suppose that the file where page.Monitoring does not strictly equal null is to be taken into consideration.

  • Here is the code.
// M11. Let's gather all relevant pages: M11, M15
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"')
    .where((page) => page.Monitoring !== null);

4.3. Step M23: break up

  • 【M23】: Break up multiple group Dataview inline fields in the file into individual groups. (pages, page ==> dictionaries, dictionary)
  • Now let’s transform from thing per date (we have single page for each date) into thing per dictionary (we want to have as many rows in a table as all dictionaries extracted from all pages).
  • The dictionary is a new row(hash) which consists of five fields sush as page.file,page.date,page.Monitoring, page.Fund,and page.ActionItem.
  • The dictionaries is a new table(array), which consists of each dictionary row(hash).

4.3.1. Take dic_20220303 for example:An array

  • file name : dic_20220303
---
Date: 2022-03-03
---

Monitoring:: FundA | Do This0303
Monitoring:: FundA | Do That0303
Monitoring:: FundB | Do Something Else0303


  • There is ==more== than one Monitoring in the original page file.
4.3.1.1. the original data structure
  • The plain page hash from a file is created by the dv.pages() function and consists of three hashes : the page.file hash, the YAML fields and the Dataview inline fields.

  • At Step M23, the following hash is transformed into three hashes, which are stored in the dictionaries array.

  • Here is a slice of the page hash from the dic_20220303 file.

{
  file: {
    name: 'dic_20220303',
    ctime: '2022-03-03T19:30:50.586+08:00',
  },
  date: '2022-03-03T00:00:00.000+08:00',
  Monitoring: ['FundA | Do This0303', 'FundA | Do That0303', 'FundB | Do Something Else0303'],
}

  • The page hash is transformed into three new hashes as follows.
4.3.1.2. the new data structure
  • The dictionaries array consists of each dictionary hash.
  • The plain dictionary hash is created from the pages at Step M23 and consists of three hashes : the page.file hash, the YAML fields and the Dataview inline fields.
  • At Step M23, the following hashes are created from the above page hash.
  • Here’s one dictionary hash, which the dictionaries array consists of.
{
  file: {
    name: 'dic_20220303',
    ctime: '2022-03-03T19:30:50.586+08:00',
  },
  date: '2022-03-03T00:00:00.000+08:00',
  Monitoring: 'FundA | Do This0303',
  Fund: 'FundA'
  ActionItem: 'Do This0303' 
}

  • Here’s another dictionary hash, which the dictionaries array consists of.
{
  file: {
    name: 'dic_20220303',
    ctime: '2022-03-03T19:30:50.586+08:00',
  },
  date: '2022-03-03T00:00:00.000+08:00',
  Monitoring: 'FundA | Do That0303',
  Fund: 'FundA'
  ActionItem: 'Do That0303'
}

  • Here’s the other dictionary hash, which the dictionaries array consists of.
{
  file: {
    name: 'dic_20220303',
    ctime: '2022-03-03T19:30:50.586+08:00',
  },
  date: '2022-03-03T00:00:00.000+08:00',
  Monitoring: 'FundB | Do Something Else0303',
  Fund: 'FundB'
  ActionItem: 'Do Something Else0303'
}

Step M24: JS arrays to Data Arrays to be sorted

// M24. dictionaries: JS arrays to Data Arrays to be sorted
// #####################################################################
dictionaries = dv.array(dictionaries);

4.4. Step M25: sort by dictionary.Fund

  • 【M25】: Sort the dictionaries array by the dictionary.Fund in ascending order.

4.5. Step M31: filter by dictionary.Fund

  • 【M31】: Suppose that the row where dictionary.Fund includes “FundQ” is to be taken into consideration.
  • Here is the code.
// M25. sort it by Fund in ascending order: 
// M31. Let's  filter it by dictionary.X : M31,M33
// #####################################################################
dictionaries = dictionaries
    .sort((dictionary) => dictionary.Fund, 'asc')
    .filter(
        (dictionary) =>
            dv.func.contains(dictionary.Fund, "FundQ")
    );

4.6. Step M41: TABLE

  • 【M41】: Display each of the list as a table.
 [
    index + 1,
    dictionary.file.link,
    dictionary.Monitoring,
    dictionary.Fund,
    dictionary.ActionItem,
]

  • Here is the code.
// M41. And let's print a table of the dictionaries
// #####################################################################
dv.header(5, "M41. Print a table of the `dictionaries`");
dv.table(
    ["N", "File", "Monitoring", "Fund", "Action Item"],
    dictionaries.map((dictionary, index) => [
        index + 1,
        dictionary.file.link,
        dictionary.Monitoring,
        dictionary.Fund,
        dictionary.ActionItem,
    ])
);

4.6.1. Example: Get name from AoH

a_Drinks=$= [ { name: "Black Coffee", price: 120, "caffeine content":300 }, { name: "Green Tea", price: 100, "caffeine content":200}, { name: "Apple Juice", price: 110, "caffeine content":0 }, { name: "Iced Chocolate", price: 130, "caffeine content":0 }, { name: "Hot Chocolate", price: 105, "caffeine content":0 }, ] .map((e) => e["name"])
//=>Black Coffee, Green Tea, Apple Juice, Iced Chocolate, Hot Chocolate


DVJS40_Output_aoh_drinks

Summary_Example

5.1.1. Main DVJS

Code Name Data type Purposes Remark
DVJS40_Output_aoh_drinks array of hashes 1.To filter by h_drink.name
2.To filter by h_drink.price
3.To filter by h_drink[“caffeine content”]
4.To sort by h_drink.name
5.To display as a table

5.1.2. Code DVJS40_Output_aoh_drinks

title: DVJS40_Output_aoh_drinks: Output aoh_drinks (array of hashes)
collapse: close
icon: 
color: 
```dataviewjs
// ### D21.define : aoh_drinks (array of hashes) ###
// #####################################################################
let aoh_drinks = [
    { name: "Black Coffee", price: 120, "caffeine content": 300 },
    { name: "Green Tea", price: 100, "caffeine content": 200 },
    { name: "Apple Juice", price: 110, "caffeine content": 0 },
    { name: "Iced Chocolate", price: 130, "caffeine content": 0 },
    { name: "Hot Chocolate", price: 105, "caffeine content": 0 },
];

// ### D21.aoh_drinks : JS arrays to Data Arrays to be sorted
// #####################################################################
aoh_drinks = dv.array(aoh_drinks);


// ### D31.aoh_drinks: filter + sort
// #####################################################################
aoh_drinks = aoh_drinks
    .filter(
        (h_drink) =>
            dv.func.contains(h_drink.name, "Coffee") ||
            dv.func.contains(h_drink.name, "Tea") ||
            dv.func.contains(h_drink.name, "Juice") ||
            dv.func.contains(h_drink.name, "Chocolate")
    )
    .filter((h_drink) => h_drink.price > 90 && h_drink.price < 125)
    .filter(
        (h_drink) => h_drink["caffeine content"] >= 0 && h_drink.price <= 300
    )
    .sort((h_drink) => h_drink.name, "asc");
    

// ### D41.Output : aoh_drinks ###
// #####################################################################
dv.header(5, "D41.Print a table of the `aoh_drinks`");
dv.table(
    ["N", "Name", "Price", "Caffeine Content"],
    aoh_drinks.map((dictionary, index) => [
        index + 1,
        dictionary.name,
        dictionary.price,
        dictionary["caffeine content"],
    ])
);

```

5.2. Screenshots(DVJS40)


Reference

Summary


2 Likes

Hey there Scholar - thx in advance for the help! Sadly, it doesn’t work. My revised code:

TABLE WITHOUT ID 
	Monitored[0] AS "Fund", 
	Monitored[1] AS "Action Item"
FROM #MonitoringFunds 
WHERE Monitoring
FLATTEN split(Monitoring, " \| ") AS "Monitored"
WHERE contains(Monitored[0], "FundA")

The error I’m getting:
"Every row during operation ‘flatten’ failed with an error; first 1:

  • No implementation of ‘split’ found for arguments: array, string"

HOWEVER - I was able to make it work by modifying your suggestion. See below:

TABLE WITHOUT ID 
	split(Monitoring, " \| ")[0] AS "Fund", 
	split(Monitoring, " \| ")[1] AS "Action Item"
FROM #MonitoringFunds 
WHERE Monitoring
FLATTEN Monitoring
WHERE contains(split(Monitoring, " \| ")[0], "FundA")

Thanks again !
2 Likes

Hi Twita,
Glad you found a variant that worked! Looking back at it now, the reason mine did not is that I forgot that you have multiple uses of Monitoring in the same file, so it is already a list. Thus you needed the FLATTEN Monitoring line on its own before trying the splits, just as you had.

Applause to @justdoitcc in the post above for noticing this issue and offering a more general and correct solution! Twita, you might want to check those out just in case you want to change your query in the future.

DQL50_alias_new_field_by_flatten : easier to read and modify

Summary_code
  • added at 2022-07-16
  • updated at 2022-09-07
    • added WHERE F_Monitoring != null

Main DQL

Code Name Data type Purposes Remark
DQL50_alias_new_field_by_flatten a string or a list 1.To define a field variable Fund by FLATTEN
2.To define a field variable ActionItem by FLATTEN
3.To filter by Fund and ActionItem
1.easier to read
2.easier to modify

Notes

Q: What does the following DQL statement mean?

FLATTEN Monitoring AS F_Monitoring
FLATTEN split(F_Monitoring, "\s+\|\s+")[0] AS Fund
FLATTEN split(F_Monitoring, "\s+\|\s+")[1] AS ActionItem

A:
1.FLATTEN Monitoring AS F_Monitoring:

  • To break up a list like Monitoring in a file into each individual Monitoring in a file
  • To define a field variable F_Monitoring as each element of Monitoring of each page
  • let F_Monitoring = each element of Monitoring of each page

PS.Monitoring: a string or a list
PS.F_Monitoring: a string

2.FLATTEN split(F_Monitoring, "\s+\|\s+")[0] AS Fund:

  • To define a field variable Fund as the expression like split(F_Monitoring, "\s+\|\s+")[0] by using FLATTEN
  • let Fund = split(F_Monitoring, "\s+\|\s+")[0];

3.FLATTEN split(F_Monitoring, "\s+\|\s+")[1] AS ActionItem:

  • To define a field variable ActionItem as the expression like split(F_Monitoring, "\s+\|\s+")[1] by using FLATTEN
  • let ActionItem = split(F_Monitoring, "\s+\|\s+")[1];

Code DQL50_alias_new_field_by_flatten :final

title: DQL50_alias_new_field_by_flatten =>For multiple Monitoring field(s) per file 1.To define a field variable `Fund` by `FLATTEN` 2.To define a field variable `ActionItem` by `FLATTEN` 3.To filter by `Fund` and `ActionItem`
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "File",
      F_Monitoring AS "Monitoring",
      Fund AS "Fund", 
	  ActionItem AS "Action Item"      
FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
WHERE Monitoring != null       
SORT file.name ASC

FLATTEN Monitoring AS F_Monitoring
WHERE F_Monitoring != null 
FLATTEN split(F_Monitoring, "\s+\|\s+")[0] AS Fund
FLATTEN split(F_Monitoring, "\s+\|\s+")[1] AS ActionItem
WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
```

Screenshots(DQL50)


2 Likes

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