Suppose that “on Monday” is corrected to “from Monday to Sunday per week”.
Topic : 2/2
Summary
- How to create a dataview table displaying the number of habits I’ve tracked per week? (P.S. Somehow have
= 1 and 
= 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)
DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE : with the FLATTEN ideas step by step
NOTE: the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter
Summary
Main DVJS
| Code Name | Data type | Group By | Purposes | Remark |
|---|---|---|---|---|
| DVJS10 _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 Exercise2.To sort by file.name in ascending order3.To define a field variable P_file_ctime4.To group by dateformat(P_file_ctime, "yyyy-WW") AS P_week5.To sort by P_week in ascending order6.To define a field variable P_target7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_Total_QTY8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday9.To display the result as a table |
NOTE: the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter P.S. You always create daily notes on time. In other words, every file.ctime is correct. |
Notes:
Summary
Q1: How to simplify the DVJS10, which is written with the FLATTEN ideas step by step?
Summary_Q1
A1_11:
Another Example: Code DVJS10_A1_11_shorter
Summary_A1_11
title: DVJS10_A1_11_shorter =>the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter
collapse: close
icon:
color:
```dataviewjs
// M010. define pages: gather all relevant pages
// M010 = T11 + T13 + T15
//
// T11.define pages: get pages from Sources
// T13.filter by Reading or Exercise
// T15.sort by file.name
//
// #####################################################################
let pages = dv
// T11
.pages('"100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"')
// T13
.where((page) => page.Reading || page.Exercise)
// T15
.sort((page) => page.file.name, "asc");
// ####
// # #
// # #
// ####
// #
// #
// #
// #
// M020.update pages: add a new field `P_file_ctime` into each page
// M020 = T31
//
// T31.update pages: add a new field `P_file_ctime` into each page
// #####################################################################
pages.forEach((page) => {
page.P_file_ctime = page.file.ctime;
});
// M030. define groups + update groups:
// M030 = T33 + T35 + T37
//
// T33.define groups: groupBy the pages + add a new field `P_week` into each group
// T35.sort groups: in ascending order
// T37.update groups: add a new field `P_target` into each group
//
// #####################################################################
let groups = pages
// T33_PART01: groupBy the pages
.groupBy((page) => dv.func.dateformat(page.P_file_ctime, "yyyy-WW"))
// T35
.sort((group) => group.key, "asc");
groups.forEach((group) => {
// T33_PART02: add a new field `P_week` into each group
group.P_week = group.key
// T37
group.P_target = "3-4x week";
});
// ###
// # #
// # #
// # #
// # #
// # # #
// ###
// #
// M040. update groups:
// M040 = T41 + T43 + T45
//
// T41.add a new field `Q_Reading_QTY` into each group
// T43.add a new field `Q_Exercise_QTY` into each group
// T45.add a new field `Q_total_QTY` into each group
//
// #####################################################################
groups.forEach((group) => {
// T41
group.Q_Reading_QTY = group.rows.Reading.filter((e) => (e === "✅")).length;
// T43
group.Q_Exercise_QTY = group.rows.Exercise.filter((e) => (e === "✅")).length;
// T45
group.Q_total_QTY = group.Q_Reading_QTY + group.Q_Exercise_QTY;
});
// ####
// # #
// # #
// ####
// # #
// # #
// # #
// # #
// M050. define groups:
// M050 = T51 + T53 + T55
//
// T51.add a new field `R_dates_of_Reading_Exercise` into each group
// T53.add a new field `R_target_met` into each group
// T55.define h_isoWeekday_Fullname_of +
// 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",
};
groups.forEach((group) => {
// T51
group.R_dates_of_Reading_Exercise = group.rows.file.link.join();
// T53
group.R_target_met = dv.func.choice(group.Q_total_QTY >= 3, "✅", "❌");
// T55_PART02: add a new field `R_fullname_of_isoWeekday` into each 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]
)
)
);
});
// M090. output pages: TABLE
// M090 = T91
//
// 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,
])
);
```
Screenshots(DVJS10_A1_11):
Code DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE
Summary_code
title: DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE =>the DQL10 = the DVJS10
collapse: close
icon:
color:
```dataviewjs
// T11. define pages: gather all relevant pages
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
// 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_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"
```
