Dataview plugin snippet showcase

Great examples. There’s not many out there on link analysis. Your post is a treatise on inlinks and outlinks.

Is it possible to do the following:

I’d like to find all outgoing links that appear in all notes in a folder, group by outgoing links with a count for each sorted desc.

Cheers!

  • Anytime!

  • It is difficult to explain it.

  • You don’t need to correct any null value. All you need to do is use the correct solution.

  • It is recommended that you should use the Conclusion in the following document because it is the correct solution.

  • You could read the DQL10, DQL20 and DQL30.

  • You may understand what happens in the DQL20_debug_sum_cash_groupBy.

  • In fact, it is necessary to understand the importance of the Conclusion. If you do not use the Conclusion, some notes are not presented in the result.

  • How to sum up a number, null, or a list of non-groupBy(or groupBy) data with skipping the null value? PS.To sum up one or two fields

So I’ve come up with this first

TABLE file.inlinks as Inlinks, length(file.inlinks) as Sum, file.outlinks as Outlinks, length(file.outlinks) as Sum, sum(length(file.inlinks)+length(file.outlinks)) as Total
FROM "path/to/folder"
SORT sum(length(file.inlinks)+length(file.outlinks)) desc, file.mtime desc

This will sort files in a folder by total of Outlinks and Inlinks. But the list will be quite big.

TABLE length(file.inlinks) as Inlinks, length(file.outlinks) as Outlinks, sum(length(file.inlinks)+length(file.outlinks)) as Total
FROM "path/to/folder"
SORT sum(length(file.inlinks)+length(file.outlinks)) desc, file.mtime desc

this is shortened to just the amounts.

I couldn’t figure out how to achieve your specific request though. But this solution should also give you an idea of “what files in a folder are the busiest”. If that’s your goal :).

Hope it helps out a bit.

Cheers

2 Likes

Very cool. Thank you!

I’ll get some learning miles out of your effort.

Hi all, love the plugin! Hopefully this is a simple question, and I just haven’t learned how to fully use it yet. :slight_smile:

I use AidenLx’s Folder Note plugin which uses a note named the same as the folder that it’s in (see example layout below).

My goal is to just show a list of folders and contents, but not the foldernote itself (the folder who’s name is the same as the parent). Here’s what the actual file layout looks like on disk:

Notes/
  Notes.md <--------- (this is a folder note)
  Subject A/
    Subject A.md <--- (this is a folder note)
    Some topic related to Subject A.md
  Subject B/
    Subject B.md <--- (this is a folder note)
    Some topic related to Subject B.md

I write a dataview list query in my Notes foldernote like this:

LIST rows.file.link
FROM "Notes"
WHERE file.folder != this.file.folder
GROUP BY link(file.folder, file.name)

The current WHERE is just to filter out the Notes.md file and only show everything else in the Notes/ folder. So, that will render this in obsidian:

- Subject A
    - Subject A
    - Some topic related to Subject A
- Subject B
    - Subject B
    - Some topic related to Subject B

I’m trying to find a way to do something like WHERE rows.file.name != rows.parent.name or something similar.

Any ideas?

Topic

Summary
  • How to get the s_file_parent_name of a file? (DQL10)
  • How to get the parent_foldernote of a file? (DQL20)

Test

Summary
  • dataview: v0.5.46

input

Summary

dictionary files:

  • Location: “100_Project/02_dataview/Q83_FolderNote/Q83_test_data”

  • filename : Q83_test_data.md
---
Date: 1990-12-31
---

### this is a folder note



folder: 1990-03

  • filename : 1990-03.md
---
Date: 1990-03-31
---

### this is a folder note



  • filename : dic_19920301.md
---
Date: 1990-03-01
---

## input
### milestones
- [type:: "food"] [desc:: "breakfast"] [cost:: 10] #Test/d01 [[Note J]] , [[Note K]]
- [type:: "food"] [desc:: "breakfast"] [cost:: 20] #Test/d02  [[Note J]]



  • filename : dic_19900306.md
---
Date: 1990-03-06
---

## input
### milestones



folder: 1990-04

  • filename : 1990-04.md
---
Date: 1990-04-30
---

### this is a folder note



  • filename : dic_19900401.md
---
Date: 1990-04-01
---

## input
### tasks
- [ ] [type:: "food"] [desc:: "breakfast"] [cost:: 100] #Test/d01 [[Note P]] , [[Note Q]]
- [ ] [type:: "food"] [desc:: "breakfast"] [cost:: 200] #Test/d02  [[Note P]]



  • filename : dic_19900406.md
---
Date: 1990-04-06
---

## input
### tasks



folder: 1990-05

  • filename : 1990-05.md
---
Date: 1990-05-31
---

### this is a folder note




DQL10_get_the_file_parent_name_of_a_file_and_LIST

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10_get_the
_file_parent_name
_of_a_file
_and_LIST
file.folder:
a string
yes 1.To define a field variable s_file_parent_name
2.To filter by file.name
3.To group by a link
4.To display the result as a list
1.The “parent link” doesn’t link to the folder note itself.
2.The Regular Expression in the DQL10 is based on the DQL10 in the following topic.
- Solutions: by Justdoitcc

Notes

Summary

Q: What does the following DQL statement mean?

FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name

To get the s_file_parent_name of a file
  • In English: To define a field variable s_file_parent_name as the expression like regexreplace(file.folder, "^(.*/)(.+)$", "$2") by using FLATTEN
  • In other words: let s_file_parent_name = regexreplace(file.folder, "^(.*/)(.+)$", "$2");
For example
  • file.path: 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03/dic_19900301.md
  • file.folder: 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03
  • s_file_parent_name: 1990-03
  • file.name: dic_19900301
  • file.ext: md

The same codes: to filter by file.name

  • To filter the data by file.name where file.name is not equal to s_file_parent_name
Original Example10
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
WHERE file.name != s_file_parent_name
Another Example11
FLATTEN split(file.folder, "/")[length(split(file.folder, "/")) - 1] AS s_file_parent_name
WHERE file.name != s_file_parent_name

DQL10_get_the_file_parent_name_of_a_file_and_LIST

Summary_code
title: DQL10_get_the_file_parent_name_of_a_file_and_LIST =>1.To define a field variable `s_file_parent_name` 2.To filter by file.name 3.To group by a link 4.To display the result as a list
collapse: close
icon: 
color: 
```dataview
LIST rows.file.link
FROM "100_Project/02_dataview/Q83_FolderNote/Q83_test_data"

FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
WHERE file.name != s_file_parent_name

GROUP BY link(file.folder, s_file_parent_name)
```

Screenshots(DQL10)


DQL20_get_the_file_parent_name_of_a_file_and_LIST

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20_get_the
_file_parent_name
_of_a_file
_and_LIST
file.folder:
a string
yes 1.To define a field variable s_file_parent_name
2.To define a field variable parent_foldernote
3.To filter by file.name
4.To group by a link
5.To display the result as a list
1.The “parent link” actually links to the folder note itself.
2.The DQL20 is based on the DQL10 and SeanWcom’s suggestion.

Notes

Summary

The same codes: To get the parent_foldernote of a file

  • Take the file dic_19900301.md for example.
    • file.path: 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03/dic_19900301.md
    • file.folder: 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03
    • file.name: dic_19900301
    • file.ext: md
Original Example20: The absolute path
the slice of DQL
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
FLATTEN file.folder + "/" + s_file_parent_name + ".md" AS parent_foldernote
the result
  • s_file_parent_name: 1990-03
  • parent_foldernote : 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03/1990-03.md
Original Example21: The absolute path
the slice of DQL
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$1$2/$2.md") AS parent_foldernote
the result
  • s_file_parent_name: 1990-03
  • parent_foldernote : 100_Project/02_dataview/Q83_FolderNote/Q83_test_data/1990-03/1990-03.md
Another Example22: The relative path
the slice of DQL
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
FLATTEN s_file_parent_name + "/" + s_file_parent_name + ".md" AS parent_foldernote
the result
  • s_file_parent_name: 1990-03
  • parent_foldernote : 1990-03/1990-03.md
Another Example23: The relative path
the slice of DQL
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2/$2.md") AS parent_foldernote
the result
  • s_file_parent_name: 1990-03
  • parent_foldernote : 1990-03/1990-03.md

DQL20_get_the_file_parent_name_of_a_file_and_LIST

Summary_code
title: DQL20_get_the_file_parent_name_of_a_file_and_LIST =>1.To define a field variable `s_file_parent_name` 2.To define a field variable `parent_foldernote` 3.To filter by file.name 4.To group by a link 5.To display the result as a list
collapse: close
icon: 
color: 
```dataview
LIST rows.file.link
FROM "100_Project/02_dataview/Q83_FolderNote/Q83_test_data"

FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS s_file_parent_name
FLATTEN file.folder + "/" + s_file_parent_name + ".md" AS parent_foldernote

WHERE file.name != s_file_parent_name
GROUP BY link(parent_foldernote)
```

Screenshots(DQL20)

Part 1/2

Part 2/2


1 Like

Fantastic, thank you! Learning more and more Dataview every day! :slight_smile: Here’s what I ended up with:

LIST rows.file.link
FROM "Notes"
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2/$2.md") AS parent_foldernote
FLATTEN regexreplace(file.folder, "^(.*/)(.+)$", "$2") AS parent_name
WHERE file.name != parent_name
GROUP BY link(parent_foldernote)

This lets me be sure that the “parent link” is actually linking to the folder note itself. So adding a second variable lets me use both the parent folder name, and the foldernote name.

Thanks!!

2 Likes

Anytime!
Thanks for your clever and useful suggestion. It has benefited me a lot.

Is it possible to set up a Dataview query that returns highlights (==text==) from a given date range? E.g. returning something like:
Note title 1:

  • Highlight 1
  • Highlight 2

Note title 2:

  • Highlight 3
  • Highlight 4

Solutions

AND !contains(School,“NFHS”)

Thanks a lot. I was helpful but unfortunately using “sum(map(rows.exercise, (e) => sum(e))) AS “sum_exercise”” of DQL20 the sums were not built, it only showed a listview.

I do use this in my own project, trying to sum values and group them by the client. This should become something like a very easy membership accounting for a club I belong.

I’m intrigued by your approach. Where do I learn more about this Google for notes? I’ve been using obsidian to talent match tech candidates against open requisitions at a fairly large tech corporation with a terrible ATS and a website from the 90s. It’s a humble effort but so far obsidian blows anything else including power BI out of the water in many ways… The blisteringly fast fulltext search with chunky search results and unlinked dimensions allows us to first filter down on a candidates job title, target level, and then adjusting from there.

The tricky part is that A the jobs are not categorized with the same profile keywords as the candidates (Backend, Microservices etc) so we are matching these tech profiles with the various skills and languages that comprise them to suggest a percentage match - and then iteratively searching from there. It’s a fascinating use case for Obsidian and I’ve been tempted to reach out to this community and ask you all how I should do it. If you have any tips or insights I’d be all ears.

Right now I have colored all of the skills and languages of a given tech stack by a color associated with the parent tech stack to interrogate the search results.Each color is ascrived an Obsidian URL so I can quickly jump from Linkedin or whatever to obsidian where the skill or language roles up to its parent profile and filters dataview tables of related candidates and jobs. This side panel has buttons and iframes that launch boolean search strings over the job site and internal candidate pools to compare against the dataview query results. Obsidian blows bothout of the water when it comes to search.



But more importantly this is when the candidate and I like to get on the call and really refine our search. It’s foolish to consider an engineer skill set unless within the context of overall impact. So Ive concatenated iframes to related internal teams, external levels.fyi salary info (filtered on Job, Location and company), our intranet (filtered on industry trends, competetve analysis docs, and message activity. Yesterday we added CSV Dataview parts to bring in previous hires by job title and team which we linked to similarity searches hacked out of an upside down LinkedIn URL I found poking around. At the end of our call I send the candidates the job stack we left off with. And tell them pick 1 to 3 of their top dream jobs so that I can reach out directly and try to seal the deal.

I wish i could say they all get jobs but the economy sucks right now. Nevertheless its totally working. The quality of conversation the candidates and I have because of obsidian is remarkable – as offers and their feedback keep attesting. But it’s still completely hacky I can’t promise anything with the economy as it is but if anyone had interest in sharing Pro tips on how they might approach such a task, I’d be happy to share any opportunities that this incredible obsidian app uncovers.

Topic

Summary
  • How to sum up one field such as exercise whose data type is a number (or a list of numbers) with using the DQL GROUP BY statement twice and skipping the null(and undefined) value? (DQL05)
  • How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a 2D array of numbers) with using the DQL GROUP BY statement once and skipping the null(and undefined) value? (DQL10)
  • How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a 2D array of numbers) with using the DQL GROUP BY statement twice and skipping the null(and undefined) value? (DQL20)
  • How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a nD array of numbers) with using groupBy once(or not) and skipping the undefined value? (DVJS10)

Test

Summary
  • dataview: v0.5.46

Input

Summary

dictionary files: Basic Case

Summary_Basic
  • Location: “100_Project/02_dataview/Q21_SumExercise/Q21_test_data”

folder: 03_number

  • filename : dic_19780301
---
Date: 1978-03-01
exercise:  60
---



  • filename : dic_19780306
---
Date: 1978-03-06
exercise: 90
---



folder: 04_list

  • filename : dic_19780401
---
Date: 1978-04-01
exercise: [20, 50]
---



  • filename : dic_19780406
---
Date: 1978-04-06
exercise: [30, 80]
---



folder: 08_null

  • filename : dic_19780801
---
Date: 1978-08-01
exercise:  
---



folder: 09_undefined

  • filename : dic_19780901
---
Date: 1978-09-01
  
---



dictionary files: Advancd Case

Summary_Advancd
  • Location: “100_Project/02_dataview/Q21_SumExercise/Q21_test_data01b”

folder: 05_2D_array

  • filename : dic_19780501
---
Date: 1978-05-01
exercise: [[10, 120]]
---



folder: 06_5D_array

  • filename : dic_19780601
---
Date: 1978-06-01
exercise: [[[[[100, 200]]]]]
---



DQL05_sum_number_or_list_with_using_groupBy_twice_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL05
_sum
_number
_or_list
_with_using
_groupBy_twice
_and_TABLE
exercise:
1.a number
2.a list of numbers
yes
(twice)
1.To sum up one field such as exercise
2.To filter by exercise
3.To group by the data twice
4.To display the result as a table

Notes

Summary

Q1: How many sum functions do I need when I design the DQL05?

Summary_Q1

A1 : for DQL05

  • One sum function reduces a list of numbers into a value. Take the following statement as example:
```dataview
      sum(exercise) as "sum_exercise"
```

  • Two sum functions reduce a 2D array of numbers into a value.
  • As a result, two sum functions reduce a list of numbers into a value where the DQL GROUP BY statement is used once. The maximum dimension of an array is 1(a list of numbers) + 1(using GROUP BY once). Take the following statement as example:
```dataview
      sum(map(rows.exercise, (e) =>  sum(e))) AS "sum_list_G1_11"
```

  • Therefore, three sum functions reduce a list of numbers into a value where the DQL GROUP BY statement is used twice. (DQL05) The maximum dimension of an array is 1(a list of numbers) + 2(using GROUP BY twice). Take the following statement as example:
```dataview
      sum(map(rows.rows.exercise, (e) => sum(sum(e)))) AS "sum_list_G2_12_OK"
```
  • Tip: Even though there are three sum functions, the following statement cannot work in the DQL05 . It returns an error message like No implementation found for 'number + array'. It means that using inner sum(e) is not enough to reduce an array into a value. As a consequence, add one inner sum function and remove one outer sum function. The following wrong sum_list_G2_21_NG becames sum_list_G2_12_OK, which is mentioned above.
```dataview
      sum(sum(map(rows.rows.exercise, (e) => sum(e)))) AS "sum_list_G2_21_NG",
```

//=>No implementation found for ‘number + array’


Code DQL05_sum_number_or_list_with_using_groupBy_twice_and_TABLE

Summary_code
title: DQL05_sum_number_or_list_with_using_groupBy_twice_and_TABLE =>1.To sum up one field such as `exercise`(type: a number or a list of numbers) 2.To filter by `exercise` 3.To group by the data twice 4.To display the result as a table
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      rows.rows.file.link AS "File",
      sum(map(rows.rows.exercise, (e) => sum(sum(e)))) AS "sum_list_G2_12_OK",
      rows.rows.exercise AS "exercise"

FROM "100_Project/02_dataview/Q21_SumExercise/Q21_test_data"

WHERE exercise
SORT file.name ASC

GROUP BY file.day.month AS G_m

GROUP BY TRUE


```

Screenshots(DQL05)


DQL10_sum_number_or_list_or_2D_array_with_using_groupBy_once_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_sum
_number
_or_list
_or_2D_array
_with_using
_groupBy_once
_and_TABLE
exercise:
1.a number
2.a list of numbers
3.a 2D array of numbers
yes
(once)
1.To sum up one field such as exercise
2.To filter by exercise
3.To group by the data once
4.To transform the group.key into the fullname_of_month
5.To display the result as a table
The DQL10 is based on the DQL20_sum_number_or_list_GROUP_BY in the following topic.
- S02: 20220725_Solutions

Notes

Summary

Q1: How many sum functions do I need when I design the DQL10?

Summary_Q1

A1 : for DQL10

  • One sum function reduces a list of numbers into a value. Take the following statement as example:
```dataview
      sum(exercise) as "sum_exercise"
```

  • Two sum functions reduce a 2D array of numbers into a value.
  • As a result, two sum functions reduce a list of numbers into a value where the DQL GROUP BY statement is used once. The maximum dimension of an array is 1(a list of numbers) + 1(using GROUP BY once). Take the following statement as example:
```dataview
      sum(map(rows.exercise, (e) => sum(e))) AS "sum_list_G1_11",
```

  • Therefore, three sum functions reduce a 2D array of numbers into a value where the DQL GROUP BY statement is used once. (DQL10) The maximum dimension of an array is 2(a 2D array of numbers) + 1(using GROUP BY once). Take the following statement as example:
```dataview
      sum(sum(map(rows.exercise, (e) => sum(e)))) AS "sum_2d_array_G1_21_OK",
      sum(map(rows.exercise, (e) => sum(sum(e)))) AS "sum_2d_array_G1_12_OK",
```

Code DQL10_sum_number_or_list_or_2D_array_with_using_groupBy_once_and_TABLE

Summary_code
title: DQL10_sum_number_or_list_or_2D_array_with_using_groupBy_once_and_TABLE =>1.To sum up one field such as `exercise`(type: a number or a list of numbers or a 2D array of numbers) 2.To filter by `exercise` 3.To group by the data once 4.To transform the group.key into the fullname_of_month 5.To display the result as a table
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      FG_month AS "G_month",
      rows.file.link AS "File",
      map(rows.exercise, (e) => sum(e)) AS "sum_number_G1_01",
      sum(map(rows.exercise, (e) => sum(e))) AS "sum_list_G1_11",
      sum(sum(map(rows.exercise, (e) => sum(e)))) AS "sum_2d_array_G1_21_OK",
      sum(map(rows.exercise, (e) => sum(sum(e)))) AS "sum_2d_array_G1_12_OK",
      rows.exercise AS "exercise"

FROM "100_Project/02_dataview/Q21_SumExercise/Q21_test_data" or "100_Project/02_dataview/Q21_SumExercise/Q21_test_data01b/05_2D_array"

WHERE exercise
SORT file.name ASC

GROUP BY file.day.month AS G_m

FLATTEN {
    "1": "January",
    "2": "February",
    "3": "March",
    "4": "April",
    "5": "May",
    "6": "June",
    "7": "July",
    "8": "August",
    "9": "September",
    "10": "October",
    "11": "November",
    "12": "December"
}[string(G_m)]  AS "FG_month"

```

Screenshots(DQL10)


DQL20_sum_number_or_list_or_2D_array_with_using_groupBy_twice_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_sum
_number
_or_list
_or_2D_array
_with_using
_groupBy_twice
_and_TABLE
exercise:
1.a number
2.a list of numbers
3.a 2D array of numbers
yes
(twice)
1.To sum up one field such as exercise
2.To filter by exercise
3.To group by the data twice
4.To display the result as a table
The DQL20 is based on the DQL10 in the topic.

Notes

Summary

Q1: How many sum functions do I need when I design the DQL20?

Summary_Q1

A1 : for DQL20

  • One sum function reduces a list of numbers into a value. Take the following statement as example:
```dataview
      sum(exercise) as "sum_exercise"
```

  • Two sum functions reduce a 2D array of numbers into a value.
  • As a result, two sum functions reduce a list of numbers into a value where the DQL GROUP BY statement is used once. Take the following statement as example:
```dataview
      sum(map(rows.exercise, (e) => sum(e))) AS "sum_list_G1_11",
```

  • Three sum functions reduce a 2D array of numbers into a value where the DQL GROUP BY statement is used once. (DQL10) The maximum dimension of an array is 2(a 2D array of numbers) + 1(using GROUP BY once). Take the following statement as example:
```dataview
      sum(sum(map(rows.exercise, (e) => sum(e)))) AS "sum_2d_array_G1_21_OK",
      sum(map(rows.exercise, (e) => sum(sum(e)))) AS "sum_2d_array_G1_12_OK",
```

  • Therefore, four sum functions reduce a 2D array of numbers into a value where the DQL GROUP BY statement is used twice. (DQL20) The maximum dimension of an array is 2(a 2D array of numbers) + 2(using GROUP BY twice). Take the following statement as example:
```dataview
      sum(map(rows.rows.exercise, (e) => sum(sum(sum(e))))) AS "sum_2d_array_G2_13_OK",
```
  • Tip: Even though there are four sum functions, the following statement cannot work in the DQL20 . It returns an error message like No implementation found for 'number + array'. It means that using inner sum(e)(or sum(sum(e))) is not enough to reduce an array into a value. As a consequence, add one inner sum function and remove one outer sum function. The following wrong sum_2d_array_G2_31_NG
    (or sum_2d_array_G2_22_NG) becames sum_2d_array_G2_13_OK, which is mentioned above.
```dataview
      sum(sum(sum(map(rows.rows.exercise, (e) => sum(e))))) AS "sum_2d_array_G2_31_NG",
      sum(sum(map(rows.rows.exercise, (e) => sum(sum(e))))) AS "sum_2d_array_G2_22_NG",
```

//=>No implementation found for ‘number + array’


Code DQL20_sum_number_or_list_or_2D_array_with_using_groupBy_twice_and_TABLE

Summary_code
title: DQL20_sum_number_or_list_or_2D_array_with_using_groupBy_twice_and_TABLE =>1.To sum up one field such as `exercise`(type: a number or a list of numbers or a 2D array of numbers) 2.To filter by `exercise` 3.To group by the data twice 4.To display the result as a table
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID
      rows.rows.file.link AS "File",
      sum(map(rows.rows.exercise, (e) => sum(sum(sum(e))))) AS "sum_2d_array_G2_13_OK",
      rows.rows.exercise AS "exercise"

FROM "100_Project/02_dataview/Q21_SumExercise/Q21_test_data" or "100_Project/02_dataview/Q21_SumExercise/Q21_test_data01b/05_2D_array"

WHERE exercise
SORT file.name ASC

GROUP BY file.day.month AS G_m

GROUP BY TRUE


```

Screenshots(DQL20)


DVJS10_sum_number_or_list_or_nD_array_and_TABLE_last_row_Total_seperately

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10
_sum
_number
_or_list
_or_nD_array
_and_TABLE
_last_row_Total_seperately
exercise:
1.a number
2.a list of numbers
3.a nD array of numbers
(n>=2)
yes
(once)
1.To sum up one field such as exercise
2.To filter by exercise
3.To group by the data once
4.To transform the group.key into the fullname_of_month
5.To display the result as a table
6.To get i_Total_exercise
7.To display the last row Total as a table and hide the file count in the dataview table
The DVJS10 is based on the DVJS20 in the following topic.
- S02:20220725_Solutions

Notes

Summary

Q1: How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a 2D array of numbers) with skipping the undefined value? (M41)(last_row_Total)

Summary_Q1

A1:

title: DVJS10 >> Q1: To sum up one field such as `exercise` whose data type is a number (or a list of numbers)(or a 2D array of numbers) (M41)(last_row_Total)
collapse: open
icon: 
color: 
```dataviewjs
// T11. define pages: 
// #####################################################################
let pages = {};


// T13. define pages.exercise: a 2D array of numbers 
// #####################################################################
// pages["exercise"] = [60, [20, 50]]; // a JS array
pages["exercise"] = [
    [2, 9, 4],
    [7, 5, 3],
    [6, 1, 8],
];


// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
// Remarked by Justdoitcc 2022-11-21 22:30
// last_row_Total_CASE_11: For a number or a list of numbers
// let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);

// last_row_Total_CASE_12: For a number or a list of numbers or a 2D array of numbers
let i_Total_exercise = dv.func.default(
    dv.func.sum(pages.exercise.map((e) => dv.func.sum(e))),
    0
);


// T90. output i_Total_exercise: 
// #####################################################################
dv.span(i_Total_exercise);

```

Screenshots(A1)

```md
45
```

Q2: How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a nD array of numbers) with skipping the undefined value? (M41)(last_row_Total)(n>=2)

Summary_Q2

A2:

title: DVJS10 >> Q2: To sum up one field such as `exercise` whose data type is a number (or a list of numbers)(or a nD array of numbers) (M41)(last_row_Total)(n>=2)
collapse: open
icon: 
color: 
```dataviewjs
// T11. define pages: 
// #####################################################################
let pages = {};


// T13. define pages.exercise: a nD array of numbers (n>=2)
// #####################################################################
// a JavaScript array
pages["exercise"] = [60, [20, 50], [[[[[100, 200]]]]]];


// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
// Remarked by Justdoitcc 2022-11-21 22:30
// last_row_Total_CASE_11: For a number or a list of numbers
// let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// last_row_Total_CASE_12: For a number or a list of numbers or a 2D array of numbers
// let i_Total_exercise = dv.func.default(
//     dv.func.sum(pages.exercise.map((e) => dv.func.sum(e))),
//     0
// );

// last_row_Total_CASE_13: For a number or a list of numbers or a nD array of numbers
let i_Total_exercise = pages.exercise
    // .array()        // To convert a Dataview data array into a JavaScript array
    .flat(Infinity) // To flatten a nD Arrays into 1D array
    .reduce((acc, value) => acc + value);


// T90. output i_Total_exercise: 
// #####################################################################
dv.span(i_Total_exercise);

```

Screenshots(A2)

```md
430
```

Q3: How to sum up one field such as exercise whose data type is a number (or a list of numbers)(or a nD array of numbers) of groupBy data with skipping the undefined value? (M13.FR12)(n>=2)

Summary_Q3

A3:

title: DVJS10 >> Q3: To sum up one field such as `exercise` whose data type is a number (or a list of numbers)(or a nD array of numbers)(M13.FR12)(table_Total)(n>=2) 
collapse: open
icon: 
color: 
```dataviewjs+
// T11. define pages: 
// #####################################################################
//let pages = {};


// T13. define pages.exercise: a nD array of numbers (n>=2)
// #####################################################################
//pages["exercise"] = [60, [20, 50], [[[[[100, 200]]]]]]; // a JS array


    // M13.FR12 TABLE:
    // #####################################################################
    dv.table(
        ["N", "File", "sum_exercise", "exercise"],
        group.rows
            .sort((k) => k.file.name, "asc")
            .map((k, index) => [
                index + 1,
                k.file.link,
                // Remarked by Justdoitcc 2022-11-21 22:20
                // G1_table_Total_CASE_11:For a number or a list of numbers
                // dv.func.sum(k.exercise),
                // G1_table_Total_CASE_12:For a number or a list of numbers or a 2D array of numbers
                // dv.func.sum(dv.func.sum(k.exercise)),
                
                // G1_table_Total_CASE_13:For a number or a list of numbers or a nD array of numbers
                dv
                    .array(k.exercise)
                    .array()         // To convert a Dataview data array into a JavaScript array
                    .flat(Infinity)  // To flatten a nD Arrays into 1D array
                    .reduce((acc, value) => acc + value), 

                
                k["exercise"],
            ])
    );


// T90. output i_Total_exercise: 
// #####################################################################
//dv.span(i_Total_exercise);

```

Code DVJS10_sum_number_or_list_or_nD_array_and_TABLE_last_row_Total_seperately

Summary_code
title: DVJS10_sum_number_or_list_or_nD_array_and_TABLE_last_row_Total_seperately =>1.To sum up one field such as `exercise`(type: a number or a list of numbers or a nD array of numbers) 2.To filter by `exercise` 3.To group by the data once 4.To transform the group.key into the fullname_of_month 5.To display the result as a table 6.To get `i_Total_exercise` 7.To display the last row Total as a table and hide the file count in the dataview table
collapse: open
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages 
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q21_SumExercise/Q21_test_data" or "100_Project/02_dataview/Q21_SumExercise/Q21_test_data01b"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.file.name !== dv.current().file.name)
    .where((page) => page.exercise !== undefined)
    .where((page) => page.file.day.year === 1978)
    .sort((page) => page.file.name, "asc");


// M13. GROUP BY file.day.month:
// #####################################################################
dv.header(2, "M13.Exercise Report groupBy Month in 1978");
for (let group of pages.groupBy((page) => page.file.day.month)) {

    
    // M13.FR10 transform the group.key into the fullname_of_month:
    // #####################################################################
    //dv.header(3, group.key);
    dv.header(3, 
        {
          "1": "January",
          "2": "February",
          "3": "March",
          "4": "April",
          "5": "May",
          "6": "June",
          "7": "July",
          "8": "August",
          "9": "September",
          "10": "October",
          "11": "November",
          "12": "December"
        }[group.key]
    );
    
    
    // M13.FR12 TABLE:
    // #####################################################################
    dv.table(
        ["N", "File", "sum_exercise", "exercise"],
        group.rows
            .sort((k) => k.file.name, "asc")
            .map((k, index) => [
                index + 1,
                k.file.link,
                
                // Remarked by Justdoitcc 2022-11-21 22:20
                // G1_table_Total_CASE_11:For a number or a list of numbers
                // dv.func.sum(k.exercise),
                // G1_table_Total_CASE_12:For a number or a list of numbers or a 2D array of numbers
                // dv.func.sum(dv.func.sum(k.exercise)),
                
                // G1_table_Total_CASE_13:For a number or a list of numbers or a nD array of numbers
                dv
                    .array(k.exercise)
                    .array()         // To convert a Dataview data array into a JavaScript array
                    .flat(Infinity)  // To flatten a nD Arrays into 1D array
                    .reduce((acc, value) => acc + value), 

                
                k["exercise"],
            ])
    );

}


// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
// Remarked by Justdoitcc 2022-11-21 22:30
// last_row_Total_CASE_11: For a number or a list of numbers
// let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// last_row_Total_CASE_12: For a number or a list of numbers or a 2D array of numbers
// let i_Total_exercise = dv.func.default(
//     dv.func.sum(pages.exercise.map((e) => dv.func.sum(e))),
//     0
// );

// last_row_Total_CASE_13: For a number or a list of numbers or a nD array of numbers
let i_Total_exercise = pages.exercise
    .array()        // To convert a Dataview data array into a JavaScript array
    .flat(Infinity) // To flatten a nD Arrays into 1D array
    .reduce((acc, value) => acc + value);


// M51. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353



// M53. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
//     ["Name", "Price", "Caffeine Content"],
//     [
//         ["Black Coffee", 120, 300],
//         ["Green Tea", 100, 200],
//         ["Apple Juice", 110, 0],
//         ["Iced Chocolate", 130, 0],
//         ["Hot Chocolate", 105, 6],
//     ]
// );

dv.table(
    ["", "exercise", "Total"],
    [
        ["**Total**", i_Total_exercise, i_Total_exercise],
    ]
);


// M61. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let a_unique_months = pages.file.day.month.where((e) => !(this[e] = e in this));
//dv.span(a_unique_months);//=>2,3


// M63. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let i_counts_of_month = a_unique_months.length;
//dv.span(i_counts_of_month);//=>2


// M65. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = i_counts_of_month + 1 ;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";



```

Screenshots(DVJS10)

Part 1/2

Part 2/2


Conclusion

Summary

To sum up one field:

  • According to the codes above, there is a summary as follows.

Basic Case: It supports a list of numbers at most.

Field Name Data type Group By Purpose DQL Expression DVJS Expression (Table vs. last_row_Total) Reamrk
exercise 1.a number
2.a list of numbers
no To sum up one field sum(exercise) AS “sum_exercise” 1.M21: (not using GROUP BY)
dv.func.sum(page.exercise),

2.M41: (not using GROUP BY)
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
1.S02 > DQL10:
20220725_Solutions

2.S2 > DVJS10
exercise 1.a number
2.a list of numbers
yes
(once)
To sum up one field sum(map(rows.exercise, (e) => sum(e))) AS “sum_list_G1_11” 1.M13.FR12: (using GROUP BY once)
dv.func.sum(k.exercise),

2.M41: (not using GROUP BY)
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
1.S02 > DQL20:
20220725_Solutions

2.S02 > DVJS20
exercise 1.a number
2.a list of numbers
yes
(twice)
To sum up one field sum(map(rows.rows.exercise, (e) => sum(sum(e)))) AS “sum_list_G2_12_OK” DQL05

Advancd Case: It supports a 2D array of numbers at most.

Field Name Data type Group By Purpose DQL Expression DVJS Expression (Table vs. last_row_Total) Reamrk
exercise 1.a number
2.a list of numbers
3.a 2D array of numbers
yes
(once)
To sum up one field 1. sum(sum(map(rows.exercise, (e) => sum(e)))) AS “sum_2d_array_G1_21_OK”
2. sum(map(rows.exercise, (e) => sum(sum(e)))) AS “sum_2d_array_G1_12_OK”
DQL10
exercise 1.a number
2.a list of numbers
3.a 2D array of numbers
yes
(twice)
To sum up one field sum(map(rows.rows.exercise, (e) => sum(sum(sum(e))))) AS “sum_2d_array_G2_13_OK” DQL20

Advancd Case(DVJS): It supports a nD array of numbers at most. (n>=2)

Field Name Data type Group By Purpose DQL Expression DVJS Expression (Table vs. last_row_Total) Reamrk
exercise 1.a number
2.a list of numbers
3.a nD array of numbers
(n>=2)
yes
(once)
To sum up one field 1.M13.FR12: (using groupBy once)
dv.array(k.exercise).array().flat(Infinity).reduce((acc, value) => acc + value, 0),

2.M41: (not using groupBy)
let i_Total_exercise = pages.exercise.array().flat(Infinity).reduce((acc, value) => acc + value, 0);
DVJS10

Hello. I’m getting myself confused with WHERE and DUR.

How can I pull a table of the Daily Notes for a specific week to put in a following Weekly Note? I know I can’t do it from the name of the weekly note. And as some daily notes are created later (when I do catch-up) it can’t be the file.day. I think I need to do it from the Daily Note’s name, and from a variable stored in the weekly note where I specify the start (or should it be end) date of the week.

Can anyone give me some ideas please?

1 Like

Topic

Summary
  • How to get daily notes for a specific week via the format like “yyyy_WW”? (DQL10)
  • How to get daily notes for a specific week via the format like “yyyy_WW”(To offset one week)? (DQL20)
  • How to design a weekly note?
    • DQL10_Q1 : via this.startDateOfWeek like “1974-02-21”
    • DQL10_Q2 : via this.yyyy_WW like “1974_08”
    • DQL10_Q3 : via this.file.name like “1974_08”
    • DQL10_Q4 : via this.file.cday (PS. I have never created the weekly notes later.)

Test

Summary
  • dataview: v0.5.46

Input

Summary

the current weekly note

Summary_weekly_note
  • filename : 1974W08 // DQL10_Q3 used
  • this.file.cday = date(“1974-02-21”) // DQL10_Q4 used
  • The YAML field startDateOfWeek(or endDateOfWeek) is used by the DQL10 or DQL10_Q1.
  • The YAML field yyyy_WW is used by the DQL10_Q2.
---
yyyy_WW : 1974_08
startDateOfWeek : 1974-02-18
endDateOfWeek   : 1974-02-24
---

### DQL10


### DQL20



dictionary files

Summary_daily_notes
  • Location: “100_Project/02_dataview/Q22_WeeklyNote/Q22_test_data”

folder: 1974_08 (“yyyy_WW”)

Summary_1974_08
  • filename : 1974-02-18
---
Date: 1974-02-18
workout: 30 minutes
---


  • filename : 1974-02-19
---
Date: 1974-02-19
workout: 60 minutes
---


  • filename : 1974-02-20
---
Date: 1974-02-20
workout: 90 minutes
---


  • filename : 1974-02-21
---
Date: 1974-02-21
workout: 120 minutes
---


  • filename : 1974-02-22
---
Date: 1974-02-22
workout: 90 minutes
---



  • filename : 1974-02-23
---
Date: 1974-02-23
workout: 60 minutes
---



  • filename : 1974-02-24
---
Date: 1974-02-24
workout: 30 minutes
---



folder: 1974_09 (“yyyy_WW”)

Summary_1974_09
  • filename : 1974-02-25
---
Date: 1974-02-25
workout: 20 minutes
---



  • filename : 1974-02-26
---
Date: 1974-02-26
workout: 50 minutes
---



  • filename : 1974-02-27
---
Date: 1974-02-27
workout: 70 minutes
---



  • filename : 1974-02-28
---
Date: 1974-02-28
workout: 80 minutes
---



  • filename : 1974-03-01
---
Date: 1974-03-01
workout: 70 minutes
---



  • filename : 1974-03-02
---
Date: 1974-03-02
workout: 50 minutes
---



  • filename : 1974-03-03
---
Date: 1974-03-03
workout: 20 minutes
---



DQL10_get_daily_notes_for_a_specific_week_via_yyyy_WW

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_get_daily_notes
_for_a_specific_week
_via_yyyy_WW
startDateOfWeek:
a string like yyyy-MM-dd
(in the current weekly note)
no 1.To filter by workout
2.To filter by date(file.name)
3.To filter by the format like “yyyy_WW”
4.To sort by file.name in ascending order
5.To display the result as a table
To require the YAML field startDateOfWeek in the current weekly note

Notes

Summary

Q1: What is the same code to get the daily notes for a specific week compared with this.startDateOfWeek?

Summary_Q1
Original Example: Q1 (To be modified)
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek , "yyyy_WW")
```

A1:

Another Example: A1_11
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.endDateOfWeek , "yyyy_WW")
```
Another Example: A1_12
```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = this.startDateOfWeek.year
WHERE date(file.name).weekyear = this.startDateOfWeek.weekyear
```

Q2: How to modify the following code to get the daily notes for a specific week compared with this.yyyy_WW like “1974_08”? (PS. Sometimes I create the weekly notes later.)

Summary_Q2
Original Example: Q2 (To be modified)
  • filename : 1974W08(the current weekly note)
---
yyyy_WW : 1974_08
startDateOfWeek : 1974-02-18
endDateOfWeek   : 1974-02-24
---
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek , "yyyy_WW")
```

A2:

Another Example: A2_21
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = this.yyyy_WW
```
Another Example: A2_22
```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = number(split(this.yyyy_WW, "_")[0] )
WHERE date(file.name).weekyear = number(split(this.yyyy_WW, "_")[1])
```

Q3: How to modify the following code to get the daily notes for a specific week compared with this.file.name like “2022W38”? (PS. Sometimes I create the weekly notes later.)

Summary_Q3
Original Example: Q3 (To be modified)
  • filename : 1974W08 // the current weekly note
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek , "yyyy_WW")
```

A3:

Another Example: A3_31
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = replace(this.file.name, "W", "_")
```
Another Example: A3_32
```dataview
WHERE date(file.name).year = number(split(this.file.name, "W")[0])
WHERE date(file.name).weekyear = number(split(this.file.name, "W")[1])
```

Q4: How to modify the following code to get the daily notes for a specific week compared with this.file.cday? (PS. I have never created the weekly notes later.)

Summary_Q4
Original Example: Q4 (To be modified)
  • filename : 1974W08 // the current weekly note
  • this.file.cday = date(“1974-02-21”) // DQL10_Q4 used
---
yyyy_WW : 1974_08
startDateOfWeek : 1974-02-18
endDateOfWeek   : 1974-02-24
---
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek , "yyyy_WW")
```

A4:

Another Example: A4_41
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.file.cday , "yyyy_WW")
```
Another Example: A4_42
```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = this.file.cday.year
WHERE date(file.name).weekyear = this.file.cday.weekyear
```

Code DQL10_get_daily_notes_for_a_specific_week_via_yyyy_WW

Summary_code
title: DQL10_get_daily_notes_for_a_specific_week_via_yyyy_WW => 0.To require the YAML field `startDateOfWeek` in the current weekly note 1.To filter by `workout` 2.To filter by `date(file.name)` 3.To filter by the format like "yyyy_WW" 4.To sort by `file.name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      workout AS "workout",    
      dateformat(date(file.name), "yyyy_WW") AS "Daily Notes",
      dateformat(this.startDateOfWeek , "yyyy_WW")  AS "Current Weekly Note"

FROM "100_Project/02_dataview/Q22_WeeklyNote/Q22_test_data"
WHERE workout

WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek , "yyyy_WW")

SORT file.name ASC

```

Screenshots(DQL10)


DQL20_get_daily_notes_for_a_specific_week_via_yyyy_WW_and_offset_one_week

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_get_daily_notes
_for_a_specific_week
_via_yyyy_WW
_and
_offset_one_week
startDateOfWeek:
a string like yyyy-MM-dd
(in the current weekly note)
no 1.To filter by workout
2.To filter by date(file.name)
3.To filter by the format like “yyyy_WW”(To offset one week)
4.To sort by file.name in ascending order
5.To display the result as a table
To require the YAML field startDateOfWeek in the current weekly note

Notes

Summary

Q1: What is the same code to get the daily notes for a specific week compared with this.startDateOfWeek(To offset one week)?

Summary_Q1
Original Example: Q1 (To be modified)
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")
```

A1:

Another Example: A1_11
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.endDateOfWeek + dur("1 week") , "yyyy_WW")
```
Another Example: A1_12

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = this.startDateOfWeek.year
WHERE date(file.name).weekyear = this.startDateOfWeek.weekyear + 1 
```
Another Example: A1_13

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = string(this.startDateOfWeek.year) + "_" + padleft(string(this.startDateOfWeek.weekyear + 1), 2, "0")
```

Q2: How to modify the following code to get the daily notes for a specific week compared with this.yyyy_WW like “1974_08”(To offset one week)? (PS. Sometimes I create the weekly notes later.)

Summary_Q2
Original Example: Q2 (To be modified)
  • filename : 1974W08 // the current weekly note
---
yyyy_WW : 1974_08
startDateOfWeek : 1974-02-18
endDateOfWeek   : 1974-02-24
---
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")
```

A2:

Another Example: A2_21

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = number(split(this.yyyy_WW, "_")[0] )
WHERE date(file.name).weekyear = number(split(this.yyyy_WW, "_")[1]) + 1
```
Another Example: A2_22

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = split(this.yyyy_WW, "_")[0] + "_" + padleft(string(number(split(this.yyyy_WW, "_")[1]) + 1), 2, "0")
```

Q3: How to modify the following code to get the daily notes for a specific week compared with this.file.name like “2022W38”(To offset one week)? (PS. Sometimes I create the weekly notes later.)

Summary_Q3
Original Example: Q3 (To be modified)
  • filename : 1974W08 // the current weekly note
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")
```

A3:

Another Example: A3_31

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name).year = number(split(this.file.name, "W")[0])
WHERE date(file.name).weekyear = number(split(this.file.name, "W")[1]) + 1 
```
Another Example: A3_32

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = split(this.file.name, "W")[0] + "_" + padleft(string(number(split(this.file.name, "W")[1]) + 1), 2, "0")
```

Q4: How to modify the following code to get the daily notes for a specific week compared with this.file.cday(To offset one week)? (PS. I have never created the weekly notes later.)

Summary_Q4
Original Example: Q4 (To be modified)
  • filename : 1974W08 // the current weekly note
  • this.file.cday = date(“1974-02-21”) // DQL10_Q4 used
---
yyyy_WW : 1974_08
startDateOfWeek : 1974-02-18
endDateOfWeek   : 1974-02-24
---
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")
```

A4:

Another Example: A4_41
```dataview
WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.file.cday + dur("1 week") , "yyyy_WW")
```
Another Example: A4_42

Limitation: Suppose that the year of the daily notes is the same as this.startDateOfWeek.year .

```dataview
WHERE date(file.name) != null
WHERE date(file.name).year = this.file.cday.year
WHERE date(file.name).weekyear = this.file.cday.weekyear + 1
```

Code DQL20_get_daily_notes_for_a_specific_week_via_yyyy_WW_and_offset_one_week

Summary_code
title: DQL20_get_daily_notes_for_a_specific_week_via_yyyy_WW_and_offset_one_week => 0.To require the YAML field `startDateOfWeek` in the current weekly note 1.To filter by `workout` 2.To filter by `date(file.name)` 3.To filter by the format like "yyyy_WW"(To offset one week) 4.To sort by `file.name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      workout AS "workout",    
      dateformat(date(file.name), "yyyy_WW") AS "Daily Notes",
      dateformat(this.startDateOfWeek , "yyyy_WW")  AS "Current Weekly Note",
      dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")  AS "Current Weekly Note(offset one week)"
      
FROM "100_Project/02_dataview/Q22_WeeklyNote/Q22_test_data"
WHERE workout

WHERE date(file.name) != null
WHERE dateformat(date(file.name), "yyyy_WW") = dateformat(this.startDateOfWeek + dur("1 week") , "yyyy_WW")

SORT file.name ASC

```

Screenshots(DQL20)


Conclusion

Summary
  • According to the codes and notes above, there is a summary as follows.
  • To get daily notes for a specific week via the format like “yyyy_WW”(To offset n weeks or not), the better solution is using the field startDateOfWeek.
  • As a result, the original YAML fields in the current weekly note can be simplified as follows.

  • filename : 1974W08 // You can rename it as needs.
---
startDateOfWeek : 1974-02-18
---
endDateOfWeek :: `=this.startDateOfWeek + dur("6 days")`   
yyyy_WW :: `=dateformat(this.startDateOfWeek , "yyyy_WW")`


### DQL10


### DQL20




Reference

Summary

Hi Everyone,

I was wondering if anybody can help with this DVJS for metadata menu. I would like to automatically populate a metadata field with the notes that are linking to the current one but filtering only those that have a certain metadata property.

How could I approach this?

Hello, I just picked up dataview and I’m trying to make a list of tasks from my daily notes but I only want the ones with numerical text to be shown - I tried typeof() and contains() but neither worked (I’m probably just doing it wrong). Any tips?

TASK FROM "journaling/daily notes"
WHERE file.ctime >= date(2022-12-15) AND completed AND typeof(task.text) => number

I’m looking for a query that lists all notes created/modified in last 1hr, 24hrs (1 day), etc. Can anyone assist?

Ok, I got the last “day” which I’m assuming is previous 24hrs. How can I get last 1hr?

Created:

LIST
WHERE file.ctime > date(today)
SORT file.name ASC

and Modified:

LIST
WHERE file.mtime > date(today)
SORT file.name ASC