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

Topic : 1/2

Summary
  1. How to extract the Fund or ActionItem via the field Monitoring? (DQL01, DQL10, DVJS03, DVJS10)
  2. How to filter by Fund or ActionItem via the field Monitoring, such as “Monitoring:: FundQ | Do That0403”, from the Markdown files with the case_DVIF_DSS structure? (DQL10, DVJS03, DVJS10)
  3. How to transform the DQL10 query into a DVJS code with the FLATTEN methods step by step? (DVJS10, 【DQL10 > Notes > Q2】)
  4. How to filter and sort an AoH(an Array of JavaScript Objects) and render a table via a DVJS code? (DVJS01)

NOTE:

  1. The DVJS01 can help you to figure out the basic DVJS code.
  2. The DVJS10 performs the same operations as the DQL10 with the FLATTEN methods step by step.
  3. The DVJS10 can help you to figure out the DQL10, including how to use the FLATTEN operator.

Structures: case_DVIF_DSS

Summary

DVIF = Dataview Inline Fields

Definitions:

Structure Code: case_DVIF_DSS
Definitions: Repeat in one file using one DVIF with at least one field separators

DVIF: Use DVIF.
DSS: one DVIF with at least one field separators

D: one DVIF
SS: field separators. Commonly used separators include " | ", ", ", and so on.
Data Structure: a list
Original Structure Code expressed in regular expressions: case_DVIF(_DSS){1,}

  • Examples: Q06_Monitoring
  • Exercises: Q79_SumOvertime, Q87_Event

Basic Example: use one DVIF, repeated in a file

NOTE:
use one DVIF, repeated in a file
one DVIF drinks with three field separators " | "

---
Date: 2022-06-01
---

%%Column Names: "item_name", "unit_price", "caffeine_content", "item_no"%%
drinks:: Black Coffee | 120 | 300 | IT001
drinks:: Green Tea | 100 | 200 | IT002
    

Basic Example: use one DVIF, not repeated in a file

NOTE:
use one DVIF, not repeated in a file
one DVIF drinks with three field separators " | "

---
Date: 2022-06-06
---

%%Column Names: "item_name", "unit_price", "caffeine_content", "item_no"%%
drinks:: Apple Juice | 110 | 0 | IT003

    

Notes:

Summary

Q1: What is the data structure of the file dic_20220403?

Summary_Q1
Original Example: Q1 (To be explained)

NOTE:

  1. Take the following file dic_20220403 as an example.
  2. Each individual field Monitoring in the page is a string.
  3. The field Monitoring, repeated in the page, is a list of strings.
---
Date: 2022-04-03
---

Monitoring:: FundQ | Do This0403
Monitoring:: FundQ | Do That0403
Monitoring:: FundP | Do Something Else0403


A1:

Another Example: A1_11

NOTE:
Before running FLATTEN Monitoring AS OneMonitoring, the field Monitoring, repeated in the page, is an array of strings as shown below.

  • Here is a slice of the page hash, where page.file.name is dic_20220403.
```JSON
[
    {
        date: "2022-04-03T00:00:00.000Z",
        Monitoring: [
            "FundQ | Do This0403",
            "FundQ | Do That0403",
            "FundP | Do Something Else0403",
        ],
        file: {
            name: "dic_20220403",
            mtime: "2022-07-15T19:30:50.551Z",
        },
    },
]
```

Another Example: A1_12

NOTE:
After running FLATTEN Monitoring AS OneMonitoring, the Monitoring in the page is an array of strings as shown below.

  • Here is a slice of the page hash, where page.file.name is dic_20220403.
```JSON
[
    {
        date: "2022-04-03T00:00:00.000Z",
        Monitoring: [
            "FundQ | Do This0403",
            "FundQ | Do That0403",
            "FundP | Do Something Else0403",
        ],
        OneMonitoring: "FundQ | Do This0403",
        file: {
            name: "dic_20220403",
            mtime: "2022-07-15T19:30:50.551Z",
        },
    },
    {
        date: "2022-04-03T00:00:00.000Z",
        Monitoring: [
            "FundQ | Do This0403",
            "FundQ | Do That0403",
            "FundP | Do Something Else0403",
        ],
        OneMonitoring: "FundQ | Do That0403",
        file: {
            name: "dic_20220403",
            mtime: "2022-07-15T19:30:50.551Z",
        },
    },
    {
        date: "2022-04-03T00:00:00.000Z",
        Monitoring: [
            "FundQ | Do This0403",
            "FundQ | Do That0403",
            "FundP | Do Something Else0403",
        ],
        OneMonitoring: "FundP | Do Something Else0403",
        file: {
            name: "dic_20220403",
            mtime: "2022-07-15T19:30:50.551Z",
        },
    },
]
```

Q2: What is the data structure of the file dic_20220503?

Summary_Q2
Original Example: Q2 (To be explained)

NOTE:

  1. Take the following file dic_20220503 as an example.
  2. The Monitoring in the page is a string.
```yaml
---
Date: 2022-05-03
---

Monitoring:: FundK | Do This0503

```

A2_21:

Another Example: A2_21
  • Here is a slice of the page hash, where page.file.name is dic_20220503.
```JSON
[
    {
        date: "2022-05-03T00:00:00.000Z",
        Monitoring: "FundK | Do This0503",
        file: {
            name: "dic_20220503",
            mtime: "2022-07-15T14:28:31.993Z",
        },
    },
]
```

Test

Summary
  • dataview: v0.5.55

Input

Summary

dictionary files

  • Location: “100_Project/02_dataview/Q06_Monitoring/Q06_test_data”

folder: 03

  • 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

folder: 04

  • 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

folder: 05_string

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

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

folder: 09_null

  • filename : dic_20220903
---
Date: 2022-09-03
---

Monitoring::

  • filename : dic_20220908
---
Date: 2022-09-08
---

Monitoring:: 
Monitoring:: 


folder: 10_undefined

  • filename : dic_20221003
---
Date: 2022-10-03
---



DQL01_split_up_a_list_into_each_individual_element_via_FLATTEN_and_TABLE

Summary

Main DQL

Code Name Data type Purposes Remark
DQL01
_split_up_a_list
_into_each_individual_element
_via_FLATTEN
_and_TABLE
Monitoring:
1.a string
2.a list of strings

OneMonitoring:
a string
1.“Monitoring:: FundQ | Do That0403”
1.To filter by Monitoring
2.To sort by file.name in ascending order
3.To split up a list Monitoring into each individual element OneMonitoring

4.To filter by OneMonitoring

5.To display the result as a table
5.1 To output the Fund1 from the OneMonitoring
5.2 To output the ActionItem1 from the OneMonitoring
Note:
Require the files with the case_DVIF_DSS structure

Notes

Summary

Q1: How to get the s_page.Fund_DQL and s_page.ActionItem_DQL from the s_page.Monitoring via Inline DQL?

Summary_Q1

A1_11:

b_string_splited: Finished!
```md
aMonitoring=["FundA | Do This", "FundA | Do That"]
sMonitoring="FundA | Do This"

// /\s+\|\s+/ 
// ==>The spaces beside the "|" will be found and removed with "|" by `split`.
b_string_splited=`=split("FundA | Do This", "\s+\|\s+")`
//=>FundA, Do This
```

s_page.Fund_DQL: Finished!
```md
s_page.Fund=`=split("FundA | Do This", "\s+\|\s+")[0]`
//=>"FundA"

s_page.Fund_DQL=`split(Monitoring, "\s+\|\s+")[0]`
//=>【The DQL expression is Finished!】
```

s_page.ActionItem_DQL: Finished!
```md
s_page.ActionItem=`=split("FundA | Do This", "\s+\|\s+")[1]` 
//=>"Do This"  

s_page.ActionItem_DQL=`split(Monitoring, "\s+\|\s+")[1]`
//=>【The DQL expression is Finished!】
```

Q2: How to get the file.path of the page [[dic_20220303]] via Inline DQL?

Summary_Q2

A2_21:

```md
dic_20220303.file.path=`=[[dic_20220303]].file.path`
//=>100_Project/02_dataview/Q06_Monitoring/Q06_test_data/03/dic_20220303.md
```

Code DQL01_split_up_a_list_into_each_individual_element_via_FLATTEN_and_TABLE

Summary_code
title: DQL01_split_up_a_list_into_each_individual_element_via_FLATTEN_and_TABLE =>0.Require the files with the `case_DVIF_DSS` structure 1.To filter by `Monitoring` 2.To sort by `file.name` in ascending order 3.To split up a list `Monitoring` into each individual element `OneMonitoring` 4.To filter by `OneMonitoring` 5.To display the result as a table 5.1 To output the `Fund1` from the `OneMonitoring` 5.2 To output the `ActionItem1` from the `OneMonitoring`
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "File",
      Monitoring AS "Original Monitoring",
      OneMonitoring AS "One Monitoring",
      split(OneMonitoring, "\s+\|\s+")[0] AS "Fund1", 
	    split(OneMonitoring, "\s+\|\s+")[1] AS "ActionItem1"      
FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
WHERE Monitoring != null          
SORT file.name ASC


FLATTEN Monitoring AS OneMonitoring


WHERE OneMonitoring != null 

```

Screenshots(DQL01)

Part 1/2:

Part 2/2:


DVJS01_example_filter_sort_AoH_and_TABLE

Summary

Main DVJS

Code Name Data type Purposes Remark
DVJS01
_example
_filter
_sort_AoH
_and_TABLE
aoh_drinks:
an Array of Hashes
1.To filter by h_drink.item_name
2.To filter by h_drink.unit_price
3.To filter by h_drink["caffeine content"]
4.To sort by h_drink.item_name
5.To display the result as a table
NOTE:
1.The prefix “AoH” of the variable aoh_drinks means an Array of Hashes.
2.The Hashes means JavaScript Objects in the topic.

Code DVJS01_example_filter_sort_AoH_and_TABLE

Summary_code
title: DVJS01_example_filter_sort_AoH_and_TABLE=>1.To filter by `h_drink.item_name` 2.To filter by `h_drink.unit_price` 3.To filter by `h_drink["caffeine content"]` 4.To sort by `h_drink.item_name` 5.To display the result as a table
collapse: close
icon: 
color: 
```dataviewjs
// D21.define : aoh_drinks
// AoH = an Array of Hashes = an Array of JavaScript Objects
// #####################################################################
let aoh_drinks = [
    { item_name: "Black Coffee", unit_price: 120, "caffeine content": 300, item_no: "IT001" },
    { item_name: "Green Tea", unit_price: 100, "caffeine content": 200, item_no: "IT002" },
    { item_name: "Apple Juice", unit_price: 110, "caffeine content": 0, item_no: "IT003" },
    { item_name: "Iced Chocolate", unit_price: 130, "caffeine content": 0, item_no: "IT004" },
    { item_name: "Hot Chocolate", unit_price: 105, "caffeine content": 0, item_no: "IT005" },
];


// D23. transform aoh_drinks :
// transform JavaScript arrays to Dataview Data Arrays in order to sort it
// #####################################################################
aoh_drinks = dv.array(aoh_drinks);


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

// D51. render a tables : aoh_drinks
// #####################################################################
dv.header(5, "D51. Render a table: `aoh_drinks`");
dv.table(
    ["N", "Item Name", "Unit Price", "Caffeine Content", "Item No"],
    aoh_drinks.map((dictionary, index) => [
        index + 1,
        dictionary.item_name,
        dictionary.unit_price,
        dictionary["caffeine content"],
        dictionary.item_no,       
    ])
);

```

Screenshots(DVJS01)


DVJS03_split_up_a_list_into_each_individual_element_and_filter_and_TABLE

NOTE:

  1. The DVJS03 performs the same operations as the DQL10.
  2. The DVJS03 is mainly used for the files with the case_DVIF_DDD structure.
Summary

Main DVJS

Code Name Data type Purposes Remark
DVJS03
_split_up_a_list
_into_each_individual_element
_and_filter
_and_TABLE
Monitoring:
1.a string
2.a list of strings

OneMonitoring:
a string
1.“Monitoring:: FundQ | Do That0403”
The DVJS03 performs the same operations as the DQL10. Note:
Require the files with the case_DVIF_DDD(or case_DVIF_DSS) structure

Code DVJS03_split_up_a_list_into_each_individual_element_and_filter_and_TABLE

Summary_Code
title: DVJS03_split_up_a_list_into_each_individual_element_and_filter_and_TABLE =>0.Require the files with the `case_DVIF_DDD`(or `case_DVIF_DSS`) structure 1.The DVJS03 performs the same operations as the DQL10.
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: get pages from Sources (M11, M15)
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"')
    .where((page) => page.Monitoring !== null && page.Monitoring !== undefined);
    

// M23. split up a list `Monitoring` into each individual element `OneMonitoring`
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// 
// define a JavaScript array
// #####################################################################
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.Monitoring a JavaScript array + Redefine sObjectType
    // #####################################################################
    if (sObjectType === "[object notArray]") {
        page.Monitoring = [page.Monitoring];
        sObjectType = "[object Array]";
    }


    // M23.EL03: transform the data when page.Monitoring is a JavaScript array
    // #####################################################################
    for (let i = 0; i < page.Monitoring.length; i++) {
        let OneMonitoring = page.Monitoring[i];

        // When OneMonitoring is not defined
        // Use Case: dic_20221003
        // #####################################################################
        let aSplitOneMonitoring = ["", ""];
        
        if (OneMonitoring) {
            aSplitOneMonitoring = OneMonitoring.split(/\s+\|\s+/);
        }
        
        dictionaries.push({
            file: page.file,
            date: page.date,
            OneMonitoring: OneMonitoring,
            Fund: aSplitOneMonitoring[0],
            ActionItem: aSplitOneMonitoring[1],
        });
    }

}


// M24. transform dictionaries: 
// transform JavaScript arrays to Dataview Data arrays in order to sort it
// #####################################################################
dictionaries = dv.array(dictionaries);


// M25. sort by dictionary.Fund in ascending order: M99.(at the bottom) 
// M31. filter by dictionary.Fund
// M33. filter by dictionary.ActionItem
// #####################################################################
dictionaries = dictionaries
    .sort((dictionary) => dictionary.Fund, "asc")
    .filter((dictionary) => dv.func.contains(dictionary.Fund, "FundQ"))
    .filter((dictionary) => dv.func.contains(dictionary.ActionItem, "That"));


// M41. render a table: dictionaries
// #####################################################################
dv.header(5, "M41. Render a table: `dictionaries`");
dv.table(
    ["N", "File", "Monitoring", "Fund", "Action Item"],
    dictionaries.map((dictionary, index) => [
        index + 1,
        dictionary.file.link,
        dictionary.OneMonitoring,
        dictionary.Fund,
        dictionary.ActionItem,
    ])
);

```

Screenshots(DVJS03)


Explanation: the DVJS03

Summary

Overview

Main variables

  • The following table presents the main variables in the DVJS03.
  • a Hash = an JavaScript Object
Variables Data type Lifetime Declared by Remark
pages Array When the code is completed let AoH = an Array of Hashes
page Hash In the {} curly brackets
dictionaries Array When the code is completed let AoH = an Array of Hashes
dictionary Hash In the {} curly brackets
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.
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.
```JSON
{
  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'],
}
```

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 and undefined is to be taken into consideration.

  • Here is the code.
```dataviewjs
// M11. define pages: get pages from Sources (M11, M15)
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"')
    .where((page) => page.Monitoring !== null && page.Monitoring !== undefined);
```

Step M23: Split up

  • 【M23】: Split 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).

Take the dic_20220303 for example:The Monitoring is 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.
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.

```JSON
{
  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.
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.
```JSON
{
  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.
```JSON
{
  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.
```JSON
{
  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: transform JavaScript arrays to Dataview Data arrays

```dataviewjs
// M24. transform dictionaries: 
// transform JavaScript arrays to Dataview Data arrays in order to sort it
// #####################################################################
dictionaries = dv.array(dictionaries);
```

Step M25: sort by dictionary.Fund

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

Step M31: filter by dictionary.Fund

  • 【M31】: Suppose that the row where dictionary.Fund includes “FundQ” is to be taken into consideration.
  • 【M33】: Suppose that the row where dictionary.ActionItem includes “That” is to be taken into consideration.
  • Here is the code.
```dataviewjs
// M25. sort by dictionary.Fund in ascending order: M99.(at the bottom) 
// M31. filter by dictionary.Fund
// M33. filter by dictionary.ActionItem
// #####################################################################
dictionaries = dictionaries
    .sort((dictionary) => dictionary.Fund, "asc")
    .filter((dictionary) => dv.func.contains(dictionary.Fund, "FundQ"))
    .filter((dictionary) => dv.func.contains(dictionary.ActionItem, "That"));
```

Step M41: TABLE

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

  • Here is the code.
```dataviewjs
// M41. render a table: 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,
    ])
);
```

Example: Get each name from an AoH

```md
a_Drinks=`$=
[
    { item_name: "Black Coffee", unit_price: 120, "caffeine content": 300, item_no: "IT001" },
    { item_name: "Green Tea", unit_price: 100, "caffeine content": 200, item_no: "IT002" },
    { item_name: "Apple Juice", unit_price: 110, "caffeine content": 0, item_no: "IT003" },
    { item_name: "Iced Chocolate", unit_price: 130, "caffeine content": 0, item_no: "IT004" },
    { item_name: "Hot Chocolate", unit_price: 105, "caffeine content": 0, item_no: "IT005" },
]
.map((e) => e["item_name"])
`
//=>Black Coffee, Green Tea, Apple Juice, Iced Chocolate, Hot Chocolate
```

Conclusion

Summary
  • Only by knowing how to use the FLATTEN operator can many complex problems be simplified.
  • It is recommended to try to read the entire documentation, not just the DQL10 query.

Reference

Summary

DVIF Structures

Q06_Monitoring: case_DVIF_DSS


Q79_SumOvertime: case_DVIF_DSS


Q87_Event: case_DVIF_DSS


Q05_Schedule: case_DVIF_DSS

```md
---
Date: 2020-05-01
---

Schedule:: Release of Z6 book 2020-05-14

```

OR

```md
---
Date: 2020-03-01
---

Schedule:: Release of J1 book 2020-06-15
Schedule:: Release of K1 book 2020-03-08

```

Q01_TermDefinition: case_DVIF_DDD

```md
---
date: 2012-09-22
---
term:: "Name of Term W1"
definition:: "Definition of Term W1"

```

OR

```md
---
date: 2012-09-19
---
term:: "Name of Term A1"
definition:: "Definition of Term A1"

term:: "Name of Term Z1"
definition:: "Definition of Term Z1"

```

Q02_Meals: case_DVIF_DDD

```md
---
date: 2018-04-01
---

typerepas:: "meal type J"
heurerepas:: "hour meal J"
faimavantrepas:: "before hunger J"
repas:: "meal J"
faimapresrepas:: "after hunger J"

```

OR

```md
---
date: 2018-03-01
---

typerepas:: "meal type M"
heurerepas:: "hour meal M"
faimavantrepas:: "before hunger M"
repas:: "meal M"
faimapresrepas:: "after hunger M"

typerepas:: "meal type E"
heurerepas:: "hour meal E"
faimavantrepas:: "before hunger E"
repas:: "meal E"
faimapresrepas:: "after hunger E"

```

case_DVIF_DDD

```md
---
date: 2022-03-01
---
%%record_1%%
field1:: value01
field2:: value02
field3:: value03

%%record_2%%
field1:: value10
field2:: value20
field3:: value30

```

YAML Structures

Q17_Purchases: case_YAML_Y-YYY


Q27_ProjectPerson: case_YAML_Y_AoH


FLST Structures

Q92_FileLists

Q: How to use the file.lists data to design a Markdown file as a table in the database, where each record consists of multiple columns?
Answers
  • There are at least four methods to do that by using the file.lists data.
Code Name Data type Structures Headings(list items) Headings(task items) Remark
DQL61 file.lists case_FLST_HLDDD what happened today what happened today
DQL63 file.lists case_FLST_HLDDD what happened today what happened today
DQL65 file.lists case_FLST_HLDDD what happened today what happened today
DQL30 file.lists case_FLST_HLDSS other events other tasks
DQL20 file.lists case_FLST_HLSSS some events some tasks
DQL10 file.lists case_FLST_HLSS milestones main tasks

Q18_QAA: case_FLST_LD-DDD


Related resources

Summary

Q79_Push


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.

Topic : 2/2

Summary
  1. How to extract the Fund or ActionItem via the field Monitoring? (DQL01, DQL10, DVJS03, DVJS10)
  2. How to filter by Fund or ActionItem via the field Monitoring, such as “Monitoring:: FundQ | Do That0403”, from the Markdown files with the case_DVIF_DSS structure? (DQL10, DVJS03, DVJS10)
  3. How to transform the DQL10 query into a DVJS code with the FLATTEN methods step by step? (DVJS10, 【DQL10 > Notes > Q2】)
  4. How to filter and sort an AoH(an Array of JavaScript Objects) and render a table via a DVJS code? (DVJS01)

NOTE:

  1. The DVJS01 can help you to figure out the basic DVJS code.
  2. The DVJS10 performs the same operations as the DQL10 with the FLATTEN methods step by step.
  3. The DVJS10 can help you to figure out the DQL10, including how to use the FLATTEN operator.

DQL10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE

NOTE: THE OLD NAME = DQL50_alias_new_field_by_flatten

Summary
  • added on 2022-07-16
  • updated on 2022-09-07
    • Added the following expression.
    ```dataview
    WHERE OneMonitoring
    ```
    
  • updated on 2023-03-10
    • Added the【DQL10 > Notes > Q2】.

Main DQL

Code Name Data type Purposes Remark
DQL10
split_up_a_list
into_each_individual_element
_via_FLATTEN
_and_filter
_and_TABLE
Monitoring:
1.a string
2.a list of strings

OneMonitoring:
a string
1.“Monitoring:: FundQ | Do That0403”
1.To filter by Monitoring
2.To sort by file.name in ascending order
3.To split up a list Monitoring into each individual element OneMonitoring

4.To filter by OneMonitoring
5.To define a new field variable Fund
6.To define a new field variable ActionItem
7.To filter by Fund and ActionItem
8.To display the result as a table
Note:
Require the files with the case_DVIF_DSS structure


Features:
1.easier to read
2.easier to modify

Notes

Summary

Q1: What does the following DQL statement mean?

Summary_Q1
Original Example: Q1 (To be explained)
```dataview

FLATTEN Monitoring AS OneMonitoring


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

```

A1_11:

```dataview

// To split up a list `Monitoring` into each individual element `OneMonitoring`
// 1.`Monitoring`: a string or a list of string
// 2.`OneMonitoring`: a string
FLATTEN Monitoring AS OneMonitoring


WHERE OneMonitoring 


// To define a field variable `Fund` as the expression `split(F_Monitoring, "\s+\|\s+")[0]`
FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund


// To define a field variable `ActionItem` as the expression `split(F_Monitoring, "\s+\|\s+")[1]`
FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem

```

Q2: How to explain the meaning of the DQL10 with the DVJS10 code?

Summary_Q2
Original Example: Q2 (To be explained)
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      OneMonitoring 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 OneMonitoring


WHERE OneMonitoring
FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
```

A2_21:

```dataview
// T91. output pages: TABLE
// TABLE WITHOUT ID
//       file.link AS "File",
//       OneMonitoring AS "Monitoring",
//       Fund AS "Fund",
//       ActionItem AS "Action Item"
// #####################################################################
// dv.table(
//     ["File", "Monitoring", "Fund", "Action Item"],
//     pages.map((page) => [
//         page.file.link,
//         page.OneMonitoring,
//         page.Fund,
//         page.ActionItem,
//     ])
// );
TABLE WITHOUT ID
      file.link AS "File",
      OneMonitoring AS "Monitoring",
      Fund AS "Fund",
      ActionItem AS "Action Item"


// T11. define pages: get pages from Sources
// FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
// #####################################################################
// let pages = dv.pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"');
FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"


// T13. filter by Monitoring
// WHERE Monitoring != null 
// #####################################################################
// pages = pages.where(
//     (page) => page.Monitoring !== null && page.Monitoring !== undefined
// );
WHERE Monitoring != null


// T15. sort by file.name: 
// SORT file.name ASC
// #####################################################################
// pages = pages.sort((page) => page.file.name, "asc");
SORT file.name ASC


// T20. Transform page.Monitoring into a Dataview Data array:
// original page.Monitoring : a string or a list of strings
// #####################################################################
// pages.forEach((page) => {
//     page.Monitoring = dv.array(page.Monitoring);  
// });
//
// T21. To spilt up a list `Monitoring` into each individual element `OneMonitoring`
// update pages: add a new field `OneMonitoring` into each page
// FLATTEN Monitoring AS OneMonitoring
// #####################################################################
// pages = pages.flatMap((page) =>
//     page.Monitoring.map((e) => Object.assign({}, page, { OneMonitoring: e }))
// );
FLATTEN Monitoring AS OneMonitoring


// T31. filter by OneMonitoring
// WHERE OneMonitoring 
// #####################################################################
// pages = pages.where((page) => page.OneMonitoring);
WHERE OneMonitoring


// T33. update pages:
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
// FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
// #####################################################################
// pages.forEach((page) => {
//     page.Fund  = page.OneMonitoring.split(/\s+\|\s+/)[0];
//     page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
// });
FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem


// T35. filter by Fund and ActionItem
// update pages: add a new field `Fund` into each page
// WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
// #####################################################################
// pages = pages.where(
//     (page) =>
//         dv.func.contains(page.Fund, "FundQ") &&
//         dv.func.contains(page.ActionItem, "That")
// );
WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
```

Code DQL10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE

NOTE: OLD NAME = DQL50_alias_new_field_by_flatten

Summary_code
title: DQL10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE =>0.Require the files with the `case_DVIF_DSS` structure 1.To filter by `Monitoring` 2.To sort by `file.name` in ascending order 3.To split up a list `Monitoring` into each individual element `OneMonitoring` 4.To filter by `OneMonitoring` 5.To define a new field variable `Fund` 6.To define a new field variable `ActionItem` 7.To filter by `Fund` and `ActionItem` 8.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      OneMonitoring 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 OneMonitoring


WHERE OneMonitoring 
FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
```

Screenshots(DQL10)


DVJS10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE

Purposes:

  1. The DVJS10 performs the same operations as the DQL10 with the FLATTEN methods step by step.
  2. The DVJS10 can help you to figure out the DQL10, including how to use the FLATTEN operator.
Summary

Main DVJS

Code Name Data type Purposes Remark
DVJS10
split_up_a_list
into_each_individual_element
_via_FLATTEN
_and_filter
_and_TABLE
Monitoring:
1.a string
2.a list of strings

OneMonitoring:
a string
1.“Monitoring:: FundQ | Do That0403”
1.The DVJS10 performs the same operations as the DQL10 with the FLATTEN methods step by step.
2.The DVJS10 can help you to figure out the DQL10, including how to use the FLATTEN operator.
Note:
1.Require the files with the case_DVIF_DSS structure

2.the DQL10 = the DVJS10

Notes:

Summary

Q1: How to use the mutate function instead of the forEach function with the DVJS10? (T20 and T33)

Summary_Q1
Original Example: Q1 (To be modified)
```dataviewjs
// T20. Transform page.Monitoring into a Dataview Data array:
// original page.Monitoring : a string or a list of strings
// #####################################################################
pages.forEach((page) => {
    page.Monitoring = dv.array(page.Monitoring);  
});


// T21. To spilt up a list `Monitoring` into each individual element `OneMonitoring`
// #####################################################################


// T31. filter by OneMonitoring
// #####################################################################


// T33. update pages:
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
// FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
// #####################################################################
pages.forEach((page) => {
    page.Fund  = page.OneMonitoring.split(/\s+\|\s+/)[0];
    page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
});



```

A1_11:

Another Example: A1_11
Summary_A1_11
```dataviewjs
// T20. Transform page.Monitoring into a Dataview Data array:
// original page.Monitoring : a string or a list of strings
// #####################################################################
// pages.forEach((page) => {
//     page.Monitoring = dv.array(page.Monitoring);  
// });
pages = pages.mutate((page) => {
    page.Monitoring = dv.array(page.Monitoring);
});


// T21. To spilt up a list `Monitoring` into each individual element `OneMonitoring`
// #####################################################################


// T31. filter by OneMonitoring
// #####################################################################


// T33. update pages:
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
// FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
// #####################################################################
// pages.forEach((page) => {
//     page.Fund  = page.OneMonitoring.split(/\s+\|\s+/)[0];
//     page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
// });
pages = pages.mutate((page) => {
    page.Fund = page.OneMonitoring.split(/\s+\|\s+/)[0];
    page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
});
```

Q2: How to use the mutate function to simplify the DVJS10?

Summary_Q2

A2_21:

Another Example: A2_21
Summary_A2_21
```dataviewjs
// T11. define pages: get pages from Sources
// T13. filter by Monitoring
// T15. sort by file.name:
// T20. Transform page.Monitoring into a Dataview Data array:
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"')
    .where((page) => page.Monitoring !== null && page.Monitoring !== undefined)
    .sort((page) => page.file.name, "asc")
    .mutate((page) => {
        page.Monitoring = dv.array(page.Monitoring);
    });


// T21. To spilt up a list `Monitoring` into each individual element `OneMonitoring`
// update pages: add a new field `OneMonitoring` into each page
// FLATTEN Monitoring AS OneMonitoring
// #####################################################################
pages = pages.flatMap((page) =>
    page.Monitoring.map((e) => Object.assign({}, page, { OneMonitoring: e }))
);


// T31. filter by OneMonitoring
// T33. update pages:
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// WHERE OneMonitoring
// T35. filter by Fund and ActionItem
// #####################################################################
pages = pages
    .where((page) => page.OneMonitoring)
    .mutate((page) => {
        page.Fund = page.OneMonitoring.split(/\s+\|\s+/)[0];
        page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
    })
    .where(
        (page) =>
            dv.func.contains(page.Fund, "FundQ") &&
            dv.func.contains(page.ActionItem, "That")
    );


// T91. output pages: TABLE
// TABLE WITHOUT ID
//       file.link AS "File",
//       OneMonitoring AS "Monitoring",
//       Fund AS "Fund",
//       ActionItem AS "Action Item"
// #####################################################################
dv.table(
    ["File", "Monitoring", "Fund", "Action Item"],
    pages.map((page) => [
        page.file.link,
        page.OneMonitoring,
        page.Fund,
        page.ActionItem,
    ])
);

```

Code DVJS10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE

Summary_code
title: DVJS10_split_up_a_list_into_each_individual_element_via_FLATTEN_and_filter_and_TABLE  =>1.The DVJS10 performs the same operations as the DQL10 with the FLATTEN methods step by step. 2.The DVJS10 can help you to figure out the DQL10, including how to use the FLATTEN operator.
collapse: close
icon: 
color: 
```dataviewjs
// T11. define pages: get pages from Sources
// FROM "100_Project/02_dataview/Q06_Monitoring/Q06_test_data"
// #####################################################################
let pages = dv.pages('"100_Project/02_dataview/Q06_Monitoring/Q06_test_data"');


// T13. filter by Monitoring
// WHERE Monitoring != null 
// #####################################################################
pages = pages.where(
    (page) => page.Monitoring !== null && page.Monitoring !== undefined
);


// T15. sort by file.name: 
// SORT file.name ASC
// #####################################################################
pages = pages.sort((page) => page.file.name, "asc");


// T20. Transform page.Monitoring into a Dataview Data array:
// original page.Monitoring : a string or a list of strings
// #####################################################################
pages.forEach((page) => {
    page.Monitoring = dv.array(page.Monitoring);  
});


// T21. To spilt up a list `Monitoring` into each individual element `OneMonitoring`
// update pages: add a new field `OneMonitoring` into each page
// FLATTEN Monitoring AS OneMonitoring
// #####################################################################
pages = pages.flatMap((page) =>
    page.Monitoring.map((e) => Object.assign({}, page, { OneMonitoring: e }))
);


// T31. filter by OneMonitoring
// WHERE OneMonitoring
// #####################################################################
pages = pages.where((page) => page.OneMonitoring);


// T33. update pages:
// update pages: add a new field `Fund` into each page
// update pages: add a new field `ActionItem` into each page
// FLATTEN split(OneMonitoring, "\s+\|\s+")[0] AS Fund
// FLATTEN split(OneMonitoring, "\s+\|\s+")[1] AS ActionItem
// #####################################################################
pages.forEach((page) => {
    page.Fund  = page.OneMonitoring.split(/\s+\|\s+/)[0];
    page.ActionItem = page.OneMonitoring.split(/\s+\|\s+/)[1];
});


// T35. filter by Fund and ActionItem
// update pages: add a new field `Fund` into each page
// WHERE contains(Fund, "FundQ") AND contains(ActionItem, "That")
// #####################################################################
pages = pages.where(
    (page) =>
        dv.func.contains(page.Fund, "FundQ") &&
        dv.func.contains(page.ActionItem, "That")
);


// T91. output pages: TABLE
// TABLE WITHOUT ID
//       file.link AS "File",
//       OneMonitoring AS "Monitoring",
//       Fund AS "Fund",
//       ActionItem AS "Action Item"
// #####################################################################
dv.table(
    ["File", "Monitoring", "Fund", "Action Item"],
    pages.map((page) => [
        page.file.link,
        page.OneMonitoring,
        page.Fund,
        page.ActionItem,
    ])
);


```

Screenshots(DVJS10):


2 Likes

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