Dataview Table show current month events only or may be within 7 days events only

Help
2

Topic

Summary
  • How to calculate the durations from the files with the case_DVIF_DSS structure?

Test

Summary
  • dataview: v0.5.55

Input

Summary

dictionary files

  • Location: “100_Project/02_dataview/Q87_Event/Q87_test_data”

folder: 03

  • filename : dic_19990301
---
Date: 1999-03-01
---

event:: Autumn starting_A | 2022-09-09
event:: Winter starting | 2022-12-01
event:: birthday | 2022-09-06

folder: 04

  • filename : dic_19990401
---
Date: 1999-04-01
---

event:: Autumn starting_B | 2022-09-11
event:: birthday | 1992-09-21

folder: 05

  • filename : dic_19990501
---
Date: 1999-05-01
---

event:: What starting01 | 2022-08-30
event:: What starting02 | 2022-08-31
event:: Autumn starting_C | 2022-09-07
event:: Autumn starting_D | 2022-09-12
event:: What starting05 | 2022-09-20
event:: What starting06 | 2022-09-21
event:: Winter starting | 2022-12-05
event:: birthday | 1980-09-28

folder: 06_null

  • filename : dic_19990601
---
Date: 1999-06-01
---

event::

folder: 07_null

  • filename : dic_19990701
---
Date: 1999-07-01
---

event:: 
event::

DQL20_filter_by_yyyy_MM_and_durations_and_TABLE: case_DVIF_DSS

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_filter_by
_yyyy_MM_and_durations
_and_TABLE		 event:
a list of strings

F_event:
a string		 no 1.To break up a list event into each individual element F_event
2.To define a field variable F_event
3.To define three field variables, such as Title, cDate, and F_dur_days
4.To filter by dateformat(X, “yyyy_MM”), dateformat(Y, “MM”) or F_dur_days
5.To sort by cDate in ascending order
6.To display the result as a table		 Note:
Require the files with the case_DVIF_DSS structure

DQL20_filter_by_yyyy_MM_and_durations_and_TABLE

Summary_code 
title: DQL20_filter_by_yyyy_MM_and_durations_and_TABLE =>0.Require the files with the `case_DVIF_DSS` structure 1.To break up a list `event` into each individual element `F_event` 2.To define a field variable `F_event` 3.To define three field variables, such as `Title`, `cDate`, and `F_dur_days` 4.To filter by dateformat(X, "yyyy_MM"), dateformat(Y, "MM") or `F_dur_days` 5.To sort by `cDate` in ascending order 6.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      Title AS "event",
	    cDate AS "Date"
FROM "100_Project/02_dataview/Q87_Event/Q87_test_data"
WHERE event != null       


FLATTEN event AS F_event


WHERE F_event != null 
FLATTEN split(F_event, "\s+\|\s+")[0] AS Title
FLATTEN split(F_event, "\s+\|\s+")[1] AS cDate
WHERE date(cDate) != null
FLATTEN dur(date("today") - date(cDate)).days AS F_dur_days
WHERE dateformat(date(cDate),"yyyy_MM") = dateformat(date("today"),"yyyy_MM")
      OR (F_dur_days < 7 AND F_dur_days >= 0)
      OR (contains(Title, "birthday") AND dateformat(date(cDate),"MM") = dateformat(date("today"),"MM"))
SORT cDate ASC

```

Screenshots(DQL20): today=“2022-09-13”

20230309_Q87_DQL20_output_a01
20230309_Q87_DQL20_output_a011190×660 66.7 KB

Reference

Summary

Q06_Monitoring

Q06_Monitoring: 【Explanation: the DVJS03】 > 【Step M23: break up】

NOTE: Using data structures and examples to explain the FLATTEN operator

Q06_Monitoring: DQL10

2 Likes