Dataview: Function to get length of unique values OR using nested queries

[justdoitcc]

Topic

Summary

• How to sum up the Overtime from the files with the case_DVIF_DSS structure?

---

Test

Summary

• dataview: v0.5.55

---

Input

Summary

dictionary files:

• Location: “100_Project/02_dataview/Q79_SumOvertime/Q79_test_data”

folder: 03

• filename : 1976-03-01

---
Date: 1976-03-01
---
#Project/P03

work:: meeting, 08:00, 09:30
work:: project1, 09:30, 12:15
work:: project1, 12:45, 14:15
work:: meeting, 14:15, 17:30

---

folder: 04

• filename : 1976-04-01

---
Date: 1976-04-01
---
#Project/P04

work:: project2, 08:15, 12:15
work:: meeting, 13:00, 16:00

---

folder: 05

• filename : 1976-05-01

---
Date: 1976-05-01
---
#Project/P05

work:: project2, 08:15, 12:15
work:: meeting, 13:00, 16:00
work:: meeting, 16:00, 18:00

---

DQL10_sum_Overtime_with_using_groupBy_once_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL10 _sum_Overtime _with_using _groupBy_once _and_TABLE	Overtime: a number	yes (once)	1.To filter by work 2.To FLATTEN work AS W 3.To define seven field variables like Date, Project, Start, End, Tend, Tstart and Hours 4.To group by Date 5.To define two field variables like totHours and totOvertime 6.To display the result as a table	Note: Require the files with the case_DVIF_DSS structure The DQL10 is based on fii’s DQL.

Code DQL10_sum_Overtime_with_using_groupBy_once_and_TABLE

Summary_code

title: DQL10_sum_Overtime_with_using_groupBy_once_and_TABLE =>1.To filter by `work` 2.To FLATTEN work AS W 3.To define seven field variables like `Date`, `Project`, `Start`, `End`, `Tend`, `Tstart` and `Hours` 4.To group by Date 5.To define two field variables like `totHours` and `totOvertime` 6.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
	  Date AS "Date",
	  totHours AS "Total Hours",
	  totOvertime AS "Overtime"
    
FROM "100_Project/02_dataview/Q79_SumOvertime/Q79_test_data"

WHERE work
FLATTEN work AS W

FLATTEN file.name AS Date
FLATTEN split(W, "\, ")[0] AS Project
FLATTEN split(W, "\, ")[1] AS Start
FLATTEN split(W, "\, ")[2] AS End
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours

GROUP BY Date
FLATTEN round(sum(rows.Hours), 2) AS totHours
FLATTEN round(sum(rows.Hours) - 8, 2) AS totOvertime


```

---

Screenshots(DQL10):

---

DQL20_sum_Overtime_with_using_groupBy_twice_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL20 _sum_Overtime _with_using _groupBy_twice _and_TABLE	Overtime: a number	yes (twice)	1.To filter by work 2.To FLATTEN work AS W 3.To define seven field variables like Date, Project, Start, End, Tend, Tstart and Hours 4.To group by Date 5.To define two field variables like totHours and totOvertime 6.To GROUP BY true 7.To display the result as a table	Note: Require the files with the case_DVIF_DSS structure The DQL20 is based on the DQL10 in the following topic. - Solutions

Code DQL20_sum_Overtime_with_using_groupBy_twice_and_TABLE

Summary_code

title: DQL20_sum_Overtime_with_using_groupBy_twice_and_TABLE =>1.To filter by `work` 2.To FLATTEN work AS W 3.To define seven field variables like `Date`, `Project`, `Start`, `End`, `Tend`, `Tstart` and `Hours` 4.To group by Date 5.To define two field variables like `totHours` and `totOvertime` 6.To GROUP BY true 7.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
	    rows.Date AS "Date",
	    rows.totHours AS "Total Hours",
	    rows.totOvertime AS "Overtime",
      sum(rows.totOvertime) AS "Total Overtime"
    
FROM "100_Project/02_dataview/Q79_SumOvertime/Q79_test_data"

WHERE work
FLATTEN work AS W

FLATTEN file.name AS Date
FLATTEN split(W, "\, ")[0] AS Project
FLATTEN split(W, "\, ")[1] AS Start
FLATTEN split(W, "\, ")[2] AS End
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours

GROUP BY Date
FLATTEN round(sum(rows.Hours), 2) AS totHours
FLATTEN round(sum(rows.Hours) - 8, 2) AS totOvertime

GROUP BY true

```

---

Screenshots(DQL20):

---

Reference

Summary

Q24_SumDuration: DQL10

• Q24_SumDuration: Solutions

---