Help with Compound Dataview Values

Twita · July 15, 2022, 1:41am

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”.

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 !!

I-d-as · July 15, 2022, 4:13am

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!

scholarInTraining · July 15, 2022, 5:54am

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!

justdoitcc · July 15, 2022, 8:26am

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)

20220715_DQL20_output_a01695×222 15.1 KB

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

DataviewJS Snippet Showcase: multiple group Dataview inline fields in the file
- How to break up multiple group Dataview inline fields in the file into individual groups?

DataviewJS Snippet Showcase: multiple group YAML fields in the file
- How to break up multiple group YAML fields in the file into individual groups?
  - DVJS: sumant28: DataviewJS Snippet Showcase 115 at 2022-01-27

Twita · July 15, 2022, 2:23pm

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 !

scholarInTraining · July 15, 2022, 5:46pm

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.

justdoitcc · July 16, 2022, 6:00am

DQL50_alias_new_field_by_flatten : easier to read and modify

Summary_code

added at 2022-07-16
- Accoding to the suggestion of scholarInTraining, the code is finished.
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)

system · July 23, 2022, 6:01am

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

Help with Compound Dataview Values

Things I have tried

What I’m trying to do

Input

dictionary files

DQL10_split_flatten : For multiple Monitoring field(s) per file

Main DQL

Code DQL10_split_flatten

Inline DQL

Screenshots(DQL10)

DQL20_split_flatten_filter : For multiple Monitoring field(s) per file

Main DQL

Code DQL20_split_flatten_filter: final

Screenshots(DQL20)

20220715_DQL20_output_a01695×222 15.1 KB

DVJS30_flatten_filter

3.4.1. Main DVJS

3.4.2. Code DVJS30_flatten_filter

3.5. Screenshots(DVJS30)

4. Explanation: DVJS30_flatten_filter

4.1. Overview

4.1.1. Main variables

4.1.1.1. The data structure of the pages

4.1.1.2. The data structure of the page

4.2. Step M11: Sources

4.3. Step M23: break up

4.3.1. Take dic_20220303 for example：An array

4.3.1.1. the original data structure

4.3.1.2. the new data structure

Step M24: JS arrays to Data Arrays to be sorted

4.4. Step M25: sort by dictionary.Fund

4.5. Step M31: filter by dictionary.Fund

4.6. Step M41: TABLE

4.6.1. Example: Get name from AoH

DVJS40_Output_aoh_drinks

5.1.1. Main DVJS

5.1.2. Code DVJS40_Output_aoh_drinks

5.2. Screenshots(DVJS40)

Reference

DQL50_alias_new_field_by_flatten : easier to read and modify

Main DQL

Notes

Code DQL50_alias_new_field_by_flatten :final

Screenshots(DQL50)

4.1.1.1. The data structure of the `pages`

4.1.1.2. The data structure of the `page`

4.3.1. Take `dic_20220303` for example：An array