Table with values grouped by week

aledallas · October 24, 2022, 12:26pm

Things I have tried

Hi,

I’m trying to create a view of my workouts per week.

I’ve managed to create a table of the workouts grouped by week, with some high-level information:

TABLE
  "3-4x week" as target,
  length(rows.workout) as "number of workouts",
  join(rows.file.link) as "workout dates",
  choice(length(rows.workout) >= 3, "✅", "❌") as "target met"
FROM "journals"
WHERE workout
GROUP BY dateformat(file.ctime, "yyyy-WW") AS week
SORT week ASC

week	target	number of workouts	workout dates	target met
2022-40	3-4x week	1	2022_10_05
2022-42	3-4x week	2	2022_10_19, 2022_10_20

What I’m trying to do

Now I would like to add additional columns with the days from Monday to Sunday and mark with a the days I worked out.

I thought of doing so by adding e.g.
choice(any(dateformat(rows.file.ctime, "c"), (x) => x = 3), "✅", "-") as "Wed"

That however doesn’t seem to work, as the “any” function always returns “false”.

Thank you in advance!

mnvwvnm · October 24, 2022, 6:24pm

Leave here some notes examples with your metadata structure to explore a solution.
(but to clarify: in each row (by week) you want to see a list with the days with some workout? but you excluded files where there’s no workout!)

justdoitcc · October 25, 2022, 4:10am

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

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

Dataview uses luxon/iso format:Formatting

aledallas · October 25, 2022, 9:51am

Thanks a lot for your help! It helped me better understand the logic underneath.
The solution by @justdoitcc will be great for a different version of the table I’m implementing

I’ve figured out that the result of the function dateformat(rows.file.ctime, "c") is a string and not a number as I was expecting.
Therefore the any() function I had in mind (returns true if ANY of the values in the array are truth) needed to be adjusted.

Here the final solution for the additional columns, should anyone be interested:

 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "1"), "✅", "-") as "Mon",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "2"), "✅", "-") as "Tue",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "3"), "✅", "-") as "Wed",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "4"), "✅", "-") as "Thu",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "5"), "✅", "-") as "Fri",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "6"), "✅", "-") as "Sat",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "7"), "✅", "-") as "Sun"

Output:

system · November 1, 2022, 9:52am

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