Topic
Summary
- How to transform an ISO weekday into the corresponding fullname via JavaScript Objects? (P.S. “1” => "
Monday", “7” => “Sunday”) (DQL01:Q3) - How to add an additional column with the days from Monday to Sunday and mark with a
the days I worked out? (DQL01) - How to simplify complex problems by the FLATTEN? (
DQL10)
Test
Summary
- dataview: v0.5.55
Input
Summary
dictionary files:
- Location: “100_Project/02_dataview/Q19_Workout/Q19_test_data”
folder: 2022-40
- filename :
2022_10_05 - file.ctime: “2022-10-05T19:30:50”
---
workout: 60 minutes
---
folder: 2022-42
- filename :
2022_10_19 - file.ctime: “2022-10-19T19:30:50”
---
workout: 60 minutes
---
- filename :
2022_10_20 - file.ctime: “2022-10-20T19:30:50”
---
workout: 60 minutes
---
folder: 2022-43
- filename :
2022_10_24 - file.ctime: “2022-10-24T19:30:50”
---
workout: 90 minutes
---
- filename :
2022_10_25 - file.ctime: “2022-10-25T19:30:50”
---
workout: 90 minutes
---
- filename :
2022_10_26 - file.ctime: “2022-10-26T19:30:50”
---
workout: 90 minutes
---
DQL01_filter_by_workout_group_by_week_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 |
|---|---|---|---|---|
| DQL01 _filter_by_workout _group_by_week _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 workout 2.To group by week 3.To sort by week in ascending order 4.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 get the data type of the ISO weekday, which is a string from “1”-“7” (“Monday” is “1”, “Sunday” is “7”), from an ISO date via Inline DQL?
Summary_Q1
A1_11:
Another Example: A1_11
- “c”: day of the week, a string from “1”-“7” (“Monday” is “1”, “Sunday” is “7”)
```md
F_weekday_1= `=typeof(dateformat(date("2022-07-18"), "c"))`//=>"string"
F_weekday_2= `=typeof(dateformat(date("2022-07-19"), "c"))`//=>"string"
F_weekday_3= `=typeof(dateformat(date("2022-07-20"), "c"))`//=>"string"
F_weekday_4= `=typeof(dateformat(date("2022-07-21"), "c"))`//=>"string"
F_weekday_5= `=typeof(dateformat(date("2022-07-22"), "c"))`//=>"string"
F_weekday_6= `=typeof(dateformat(date("2022-07-23"), "c"))`//=>"string"
F_weekday_7= `=typeof(dateformat(date("2022-07-24"), "c"))`//=>"string"
```
Q2: How to get the ISO weekday, which is a string from “1”-“7” (“Monday” is “1”, “Sunday” is “7”), from an ISO date via Inline DQL?
Summary_Q2
A2_21:
Another Example: A2_21
- “c”: day of the week, a string from “1”-“7” (“Monday” is “1”, “Sunday” is “7”)
```md
F_weekday_1= `=dateformat(date("2022-07-18"), "c")`//=>"1"
F_weekday_2= `=dateformat(date("2022-07-19"), "c")`//=>"2"
F_weekday_3= `=dateformat(date("2022-07-20"), "c")`//=>"3"
F_weekday_4= `=dateformat(date("2022-07-21"), "c")`//=>"4"
F_weekday_5= `=dateformat(date("2022-07-22"), "c")`//=>"5"
F_weekday_6= `=dateformat(date("2022-07-23"), "c")`//=>"6"
F_weekday_7= `=dateformat(date("2022-07-24"), "c")`//=>"7"
```
Q3: How to get the fullname of the ISO weekday, which is a string from “1”-“7” (“Monday” is “1”, “Sunday” is “7”), from an ISO date via Inline DQL?
Summary_Q3
Original Example: Q3 (To be modified)
```md
fullname_of_ISO_weekday=`=
{
"1": "Monday",
"2": "Tuesday",
"3": "Wednesday",
"4": "Thursday",
"5": "Friday",
"6": "Saturday",
"7": "Sunday"
}["7"]`
//==>"Sunday"
```
A3_31: non-formatted
Another Example: A3_31
```dataview
{"1":"Monday","2":"Tuesday","3":"Wednesday","4":"Thursday","5":"Friday","6":"Saturday","7":"Sunday"}[e] AS "fullname_of_ISO_weekday"
```
A3_32:
Another Example: A3_32
```dataview
object("1","Monday","2","Tuesday","3","Wednesday","4","Thursday","5","Friday","6","Saturday","7","Sunday")[e] AS "fullname_of_ISO_weekday"
```
Code DQL01_filter_by_workout_group_by_week_and_TABLE
Summary_code
title: DQL01_filter_by_workout_group_by_week_and_TABLE =>1.To filter by `workout` 2.To group by `week` 3.To sort by `week` in ascending order 4.To display the result as a table
collapse: close
icon:
color:
```dataview
TABLE WITHOUT ID
week AS "Week",
"3-4x week" AS "Target",
length(rows.workout) AS "Workout QTY",
join(rows.file.link) AS "Workout dates",
choice(length(rows.workout) >= 3, "✅", "❌") AS "Target met",
choice(length(rows.workout) < 3,
"",
join(
map(dateformat(rows.file.ctime, "c"),
(e) =>
"✅" +
{
"1": "Monday",
"2": "Tuesday",
"3": "Wednesday",
"4": "Thursday",
"5": "Friday",
"6": "Saturday",
"7": "Sunday"
}[e]
)
)
)
AS "Day of the week"
FROM "100_Project/02_dataview/Q19_Workout/Q19_test_data"
WHERE workout
GROUP BY dateformat(file.ctime, "yyyy-WW") AS week
SORT week ASC
```
Screenshots(DQL01):
DQL10_filter_by_workout_group_by_week_and_TABLE
NOTE:
1.You always create daily notes on time. In other words, every file.ctime is correct.
2.The DQL10 performs the same operations as the DQL01. However, the DQL10 structure, with many FLATTEN operators and very simple TABLE expressions, helps to deal with complex problem.
Summary
Main DQL
| Code Name | Data type | Group By | Purposes | Remark |
|---|---|---|---|---|
| DQL10 _filter_by_workout _group_by_week _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 workout 2.To define a field variable P_file_ctime 3.To group by dateformat(P_file_ctime, "yyyy-WW") AS P_week 4.To sort by P_week in ascending order 5.To define a field variable Q_Total_QTY 6.To define two field variables, such as R_target_met and R_fullname_of_isoWeekday 7.To display the result as a table |
Note: The DQL10 does what the DQL01 does. However, the DQL10 structure, with many FLATTEN operators and very simple TABLE expressions, helps to deal with complex problem. P.S. You always create daily notes on time. In other words, every file.ctime is correct. |
Code DQL10_filter_by_workout_group_by_week_and_TABLE
Summary_code
title: DQL10_filter_by_workout_group_by_week_and_TABLE =>1.To filter by `workout` 2.To define a field variable `P_file_ctime` 3.To group by `dateformat(P_file_ctime, "yyyy-WW")` AS `P_week` 4.To sort by `P_week` in ascending order 5.To define a field variable `Q_Total_QTY` 6.To define two field variables, such as `R_target_met` and `R_fullname_of_isoWeekday` 7.To display the result as a table
collapse: close
icon:
color:
```dataview
TABLE WITHOUT ID
P_week AS "Week",
"3-4x week" AS "Target",
Q_Total_QTY AS "Workout QTY",
join(rows.file.link) AS "Workout dates",
R_target_met AS "Target met",
R_fullname_of_isoWeekday AS "Day of the week"
FROM "100_Project/02_dataview/Q19_Workout/Q19_test_data"
WHERE workout
FLATTEN file.ctime AS P_file_ctime
GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week
SORT P_week ASC
FLATTEN length(rows.workout) AS Q_Total_QTY
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):
Reference
Summary
Emojis
- White Heavy Check Mark Emoji (green tick, checkmark, checkbox, green check mark) : U+2705
Cross Mark Emoji (cross, x, ex, ex mark, multiplication sign) : U+274C