How do I create a dataview table displaying the number of habits I've tracked in a week?

Help
4

Suppose that “on Monday” is corrected to “from Monday to Sunday per week”.

Topic : 1/2

Summary
  • How to create a dataview table displaying the number of habits I’ve tracked per week? (P.S. Somehow have :white_check_mark: = 1 and :x: = 0 then get the sum) (DQL10, DQL20)
  • How to write a DVJS with the FLATTEN ideas step by step? ( DVJS10)
  • How to simplify the DVJS10, which is written with the FLATTEN ideas step by step? ( DVJS10_A1_11_shorter)

Test

Summary
  • dataview: v0.5.55

Input

Summary

dictionary files:

  • Location: “100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data”

folder: 2022-40

  • filename : 05-Oct-2022
  • file.ctime: “2022-10-05T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-05
---
Reading :: ✅
Exercise :: ❌

folder: 2022-42

  • filename : 19-Oct-2022
  • file.ctime: “2022-10-19T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-19
---
Reading ::  ❌
Exercise ::  ✅
  • filename : 20-Oct-2022
  • file.ctime: “2022-10-20T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-20
---
Reading :: ✅
Exercise :: ❌

folder: 2022-43

  • filename : 24-Oct-2022
  • file.ctime: “2022-10-24T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-24
---
Reading :: ✅
Exercise :: ❌
  • filename : 25-Oct-2022
  • file.ctime: “2022-10-25T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-25
---
Reading :: ❌
Exercise :: ✅
  • filename : 26-Oct-2022
  • file.ctime: “2022-10-26T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-26
---
Reading :: ❌
Exercise :: ✅

folder: 2022-51_null_undefined

  • filename : 21-Dec-2022
  • file.ctime: “2022-12-21T19:30:50”
---
cDate: 2022-12-21
---
Reading :: 
Exercise ::
  • filename : 22-Dec-2022
  • file.ctime: “2022-12-22T19:30:50”
---
cDate: 2022-12-22
---
Reading ::
  • filename : 23-Dec-2022
  • file.ctime: “2022-12-23T19:30:50”
---
cDate: 2022-12-23
---

Exercise ::
  • filename : 24-Dec-2022
  • file.ctime: “2022-12-24T19:30:50”
---
cDate: 2022-12-24
---

DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE :

NOTE: You always create daily notes on time. In other words, every file.ctime is correct.

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_filter_by_Reading_or_Exercise
_group_by_P_file_ctime
_and_TABLE		 dateformat(rows.file.ctime, "yyyy-WW"):
1.yyyy = four- to six- digit year, pads to 4
2.WW = ISO week number, padded to 2


dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a string from “1”-“7”
(“Monday” is “1”, “Sunday” is “7”)		 yes 1.To filter by Reading or Exercise
2.To sort by file.name in ascending order

3.To define a field variable P_file_ctime
4.To group by dateformat(P_file_ctime, "yyyy-WW") AS P_week
5.To sort by P_week in ascending order
6.To define a field variable P_target

7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_Total_QTY

8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday

9.To display the result as a table		 P.S. You always create daily notes on time. In other words, every file.ctime is correct.

Notes:

Summary

Q1: How to understand the meaning of the DQL10 in terms of the DVJS10?

Summary_Q1

A1_11:

Original Example: A1_11 (To be explained with simple Comments and corresponding DVJS10 statement)
```dataview
// T91. output pages: TABLE
// dv.table(
//     [
//         // P
//         "Week",
//         "Target",
//      
//         // Q
//         "Reading QTY",
//         "Exercise  QTY",
//         "Total QTY",
//      
//         // R
//         "Reading / Exercise dates",
//         "Target met",
//         "Day of the week",
//     ],
//     groups.map((group) => [
//         // P
//         group.P_week,
//         group.P_target,
//      
//         // Q
//         group.Q_Reading_QTY,
//         group.Q_Exercise_QTY,
//         group.Q_total_QTY,
//      
//         // R
//         group.R_dates_of_Reading_Exercise,
//         group.R_target_met,
//         group.R_fullname_of_isoWeekday,
//     ])
// );
// #####################################################################
TABLE WITHOUT ID
      // P
      P_week AS "Week",
      P_target AS "Target",
      
      // Q  
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",

      // R
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"


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


// T13. filter by Reading or Exercise
// pages = pages.where((page) => page.Reading || page.Exercise);
// #####################################################################
WHERE Reading OR Exercise


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


//   ####
//   #   #
//   #   #
//   ####
//   #
//   #
//   #
//   #


// T31. update pages: add a new field `P_file_ctime` into each page
// pages.forEach((page) => {
//     page.P_file_ctime = page.file.ctime;
// });
// #####################################################################
FLATTEN file.ctime AS P_file_ctime


// T33. define groups: groupBy the pages + add a new field `P_week` into each group
// ***************************************************************** //
// T33_PART01: groupBy the pages
// let groups = pages.groupBy((page) => dv.func.dateformat(page.P_file_ctime, "yyyy-WW"));
// ***************************************************************** //
// T33_PART02: add a new field `P_week` into each group
// groups.forEach((group) => {
//     group.P_week = group.key;
// });
// #####################################################################
GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week


// T35. sort groups: in ascending order
// groups = groups.sort((group) => group.P_week, "asc");
// #####################################################################
SORT P_week ASC


// T37. update groups: add a new field `P_target` into each group
// groups.forEach((group) => {
//     group.P_target = "3-4x week";
// });
// #####################################################################
FLATTEN "3-4x week" AS P_target



//    ###
//   #   #
//   #   #
//   #   #
//   #   #
//   # # #
//    ###
//       #


// T41. update groups: add a new field `Q_Reading_QTY` into each group
// groups.forEach((group) => {
//     group.Q_Reading_QTY = group.rows.Reading.filter((e) => (e === "✅")).length;
// });
// #####################################################################
FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY


// T43. update groups: add a new field `Q_Exercise_QTY` into each group
// groups.forEach((group) => {
//     group.Q_Exercise_QTY = group.rows.Exercise.filter((e) => (e === "✅")).length;
// });
// #####################################################################
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY


// T45. update groups: add a new field `Q_total_QTY` into each group
// groups.forEach((group) => {
//     group.Q_total_QTY = group.Q_Reading_QTY + group.Q_Exercise_QTY;
// });
// #####################################################################
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY



//   ####
//   #   #
//   #   #
//   ####
//   # #
//   #  #
//   #   #
//   #    #



// T51. update groups: add a new field `R_dates_of_Reading_Exercise` into each group
// groups.forEach((group) => {
//     group.R_dates_of_Reading_Exercise = group.rows.file.link.join();
// });
// #####################################################################
FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise


// T53. update groups: add a new field `R_target_met` into each group
// groups.forEach((group) => {
//     group.R_target_met = dv.func.choice(group.Q_total_QTY >= 3, "✅", "❌");
// });
// #####################################################################
FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met


// T55. define h_isoWeekday_Fullname_of + update groups: 
// add a new field `R_fullname_of_isoWeekday` into each group
// ***************************************************************** //
// T55_PART01: define h_isoWeekday_Fullname_of
// let h_isoWeekday_Fullname_of = {
//     "1": "Monday",
//     "2": "Tuesday",
//     "3": "Wednesday",
//     "4": "Thursday",
//     "5": "Friday",
//     "6": "Saturday",
//     "7": "Sunday",
// };
// ***************************************************************** // 
// T55_PART02: add a new field `R_fullname_of_isoWeekday` into each group
// groups.forEach((group) => {
//     group.R_fullname_of_isoWeekday = dv.func.choice(
//         group.Q_total_QTY < 3,
//         "",
//         dv.func.join(
//             dv.func.dateformat(group.rows.P_file_ctime, "c").map(
//                 (e) =>
//                     "✅" + h_isoWeekday_fullname_of[e]
//             )
//         )
//     );
// });
// #####################################################################
FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_file_ctime, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday 

```

Code DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE

Summary_code 
title: DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE  =>1.To filter by `Reading` or `Exercise` 2.To sort by `file.name` in ascending order 3.To define a field variable `P_file_ctime` 4.To group by `dateformat(P_file_ctime, "yyyy-WW")` AS `P_week` 5.To sort by `P_week` in ascending order 6.To define a field variable `P_target` 7.To define three field variables, such as `Q_Reading_QTY`, `Q_Exercise_QTY`, and `Q_Total_QTY` 8.To define three field variables, such as `R_dates_of_Reading_Exercise`, `R_target_met`, and `R_fullname_of_isoWeekday` 9.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      P_week AS "Week",
      P_target AS "Target",
      
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",
      
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"

FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"
WHERE Reading OR Exercise
SORT file.name ASC


FLATTEN file.ctime AS P_file_ctime
GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week
SORT P_week ASC
FLATTEN "3-4x week" AS P_target


FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY


FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise

FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met

FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_file_ctime, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday 

```

Screenshots(DQL10): on win7

20230219_Q26_DQL10_output_a01
20230219_Q26_DQL10_output_a011470×550 82.3 KB

Screenshots(DQL10): on win10 or android

20230219_Q26_DQL10_output_b01_win10
20230219_Q26_DQL10_output_b01_win101470×550 79.1 KB

DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE:

NOTE: Sometimes you create the daily notes later. In other words, not every file.ctime is correct.

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_filter_by_Reading_or_Exercise
_group_by_P_isoDate_from_file_name
_and_TABLE		 file.name:
1.“01-Jan-2022”
2.“01-Feb-2022”
12.“01-Dec-2022”


dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a string from “1”-“7”
(“Monday” is “1”, “Sunday” is “7”)		 yes 1.To filter by Reading or Exercise
2.To sort by file.name in ascending order

3.To define a field variable P_isoDate_from_file_name
4.To group by dateformat(P_isoDate_from_file_name, "yyyy-WW") AS P_week
5.To sort by P_week in ascending order
6.To define a field variable P_target

7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_total_QTY

8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday

9.To display the result as a table		 P.S. Sometimes you create the daily notes later. In other words, not every file.ctime is correct.

Code DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE

Summary_code 
title: DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE =>1.To filter by `Reading` or `Exercise` 2.To sort by `file.name` in ascending order 3.To define a field variable `P_isoDate_from_file_name` 4.To group by `dateformat(P_isoDate_from_file_name, "yyyy-WW")` AS `P_week` 5.To sort by `P_week` in ascending order 6.To define a field variable `P_target` 7.To define three field variables, such as `Q_Reading_QTY`, `Q_Exercise_QTY`, and `Q_total_QTY` 8.To define three field variables, such as `R_dates_of_Reading_Exercise`, `R_target_met`, and `R_fullname_of_isoWeekday` 9.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      P_week AS "Week",
      P_target AS "Target",
      
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",
      
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"

FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"
WHERE Reading OR Exercise
SORT file.name ASC


FLATTEN split(file.name, "-")[2] AS yyyy
FLATTEN split(file.name, "-")[1] AS MMM
FLATTEN split(file.name, "-")[0] AS dd


FLATTEN 
{
    "Jan": "01",
    "Feb": "02",
    "Mar": "03",
    "Apr": "04",
    "May": "05",
    "Jun": "06",
    "Jul": "07",
    "Aug": "08",
    "Sep": "09",
    "Oct": "10",
    "Nov": "11",
    "Dec": "12"
}[MMM] AS MM


FLATTEN yyyy + "-" + MM + "-" + dd AS s_isoDate
FLATTEN date(s_isoDate) AS P_isoDate_from_file_name
WHERE P_isoDate_from_file_name

GROUP BY dateformat(P_isoDate_from_file_name, "yyyy-WW") AS P_week
SORT P_week ASC
FLATTEN "3-4x week" AS P_target



FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY



FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise

FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met

FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_isoDate_from_file_name, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday


```

Screenshots(DQL20): on win7

20230219_Q26_DQL20_output_a01
20230219_Q26_DQL20_output_a011470×580 85 KB

Screenshots(DQL20): on win10 or android

20230219_Q26_DQL20_output_b01_win10
20230219_Q26_DQL20_output_b01_win101470×580 81.7 KB

Conclusion

Summary

CASE01: Every file.ctime is correct.

  • Use the DQL10 if you always create daily notes on time. (In other words, every file.ctime is correct.)

CASE02: Every file.name is correct in the form, such as “01-Jan-2022”.

  • Use the DQL20 if sometimes you create the daily notes later. (In other words, not every file.ctime is correct.)

CASE03: Every cDate is correct.

NOTE:
Suppose that every file.name is correct in the different forms, such as “01-Jan-2022”, “01_Jan_2022”, “Jan 1, 2022”, “Jan/1/2022”, “January 1, 2022”, “31-03-2022”, “31_03_2022”, “03 31, 2022”, and so on.

  • If sometimes you create the daily notes later and every cDate is correct, use the DQL10 with modifying file.ctime to date(cDate).
  • P.S. The field cDate is defined in daily notes in the example.

folder: 2022-40

  • filename : 05-Oct-2022
  • file.ctime: “2022-10-05T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-05
---
Reading :: ✅
Exercise :: ❌

Original Expression:

```dataview
FLATTEN file.ctime AS P_file_ctime
```

New Expression:

```dataview
FLATTEN date(cDate) AS P_file_ctime
```

Reference

Summary

Emojis

The main DQL

The Regular Expression

JavaScript Objects

dateformat(file.ctime, “yyyy-WW”) or dateformat(rows.file.ctime, “c”)

Implicit Fields: file.ctime, file.name, or file.day

1 Like