Neat! I wonder: Can you use the implicit file.lists
field documented here to avoid having to read the file?
Here is my DVQ:
TABLE without ID Link.up As “Main Category”,(Link + “(” + length(rows.file.link) + “)”) AS “Category”,rows.file.link AS “Thought Note”, rows.file.cday AS “Date”
FROM #z/litnote and !“81 ADMIN”
Flatten Link
Group by Link
sort Link.up ASCENDING
And a screen shot of the output:
I can’t figure out:
- get a bullet point before each “Thought Note”, and
- get the date to line up more closely to the associated note name
Any suggestions?
Denise
Neat! I wonder: Can you use the implicit
file.lists
field documented here to avoid having to read the file?
Oh nice, that’s made it a bit simpler I’m always down to improve efficiency!
I also got rid of the startDate
and just use the first bullet point for that now:
```dataviewjs
let t, s, r = dv.current().file.lists.values.filter(x => x.text.match(/^\d{4}-\d{2}-\d{2} .+/))
dv.table(['Time', 'Event'], r.map(x => {
let [_, a, b] = x.text.match(/^(\S+)\s+(.+)$/); [t, s] = s ? [moment(a).from(moment(s), true), s] : [a, a]
return [t, b]
}))```
- 2022-02-05 Started learning Javascript
- 2022-02-06 Wrote a "Hello world" script, and it worked!
- 2022-02-11 Learned how to fetch data from a URL
- 2022-02-28 Learned about Promises
- 2022-03-03 Learned about await/async
- 2022-05-02 Started working with NPM libraries
- 2022-07-10 Published my first code to Github
This should be so simple but I’m having a brain freeze.
I have a very simple DVQ that lists all open tasks:
Task from “”
WHERE !completed
That query is listed on my Daily Note Page. I want the query to NOT include any tasks listed on that page itself. My daily note page is title by date. Thus if I do this:
Task from “”
WHERE !completed and file.name !=“2022-02-17”
Then I get what I want. But I don’t want to “hard code” the date.
I tried using the date(today) type combination, but of course, that isn’t a string so it doesn’t work file.name. Also tried concatenating a string, but that didn’t work either. I can’t use file.cday because it would exclude other files created that day (not just the today’s daily note page, like I want).
Thoughts?
I feel like I’m missing something very simple and straightforward.
UPDATE: I GOT IT!
Task from “”
WHERE !completed and !contains(file.name,this.file.name)
Problem solved.
I have a tabel generated by dataviewjs.
I’m not sure if it has the same table style with dataview.
My issue is wth the above code, it is OK on the screen.
But, when I print out by “Export to pdf”, all of the contents including the header are disappeared.
The table borders are alive.
Obsidian : v0.15.6
Theme : Default
I don’t know whre I have to change to fix this. So, it would be great if someone give me a tip.
=======================
I didn’t change anything. But, It’s ok now. Maybe after restart???
Hi! I’d love some help for formatting.
I’ve started logging my meals in my daily notes.
I use this code:
TABLE typerepas as "Type de repas", heurerepas AS "Heure", faimavantrepas AS "Faim avant", repas AS "Repas", faimapresrepas AS "Faim après"
FROM "Daily notes"
WHERE typerepas != FALSE
LIMIT 15
SORT file.cday DESC
Currently it looks like this:
Is there a way to “clean it up” so that each bullet point has its own row, so that the columns are aligned for each meal instead of having the daily info and not being properly aligned?
Thanks so much!
Can one flatten multiple lists at a time? I’m not sure dataview would know how the entries correlate.
Topic : S01
How to sum up a number, null, or a list of non-groupBy(or groupBy) data with skipping the null value? PS.To sum up one or two fields
Summary
0.field cash =>type: a number, null or a list
0.field card =>type: a number, null or a list
1.DQL10 : How to sum up one or two fields such as cash and card(type: a number, null, or a list) without using groupBy?
2.DQL20 : How to sum up one field such as cash with using groupBy step by step?
3.DQL30 : How to sum up one or two fields such as cash and card(type: a number, null, or a list) with using groupBy?
4.DVJS10: How to TABLE the last_row_Total with sum(cash) and sum(card)?
5.DQL40 : How to TABLE last_row_Total merged with using groupBy and SORT file.name ASC
?
6.DQL50 : How to TABLE last_row_Total merged with using groupBy and SORT file.name ASC
? (To fix Total:111739.79999999999)
7.DQL60 : How to TABLE last_row_Total merged with using groupBy and SORT file.name DESC
?
1. Test
Summary
- by dataview_v0.5.41
2. Overview
Summary
2.1. Main DQL or DVJS
- The fowllowng table presents the main DQLs in the Solutions.
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL10_sum_not_null | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card | It is finished. |
DQL20_debug_sum_cash_groupBy | a number, null, or a list | yes | 1.To sum up one field such as cash 2.step by step |
|
DQL30_sum_GROUP_BY_G_month | a number, null, or a list | yes | 1.To sum up one or two fields such as cash and card | It is finished. |
DVJS10_sum_not_null_last_row | 1.a number ,null, a list | no | 1.get i_Total_cash 2.get i_Total_card 3.get i_Total 4.To display them as a table |
|
DQL40_sum_not_null_last_row_ASC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name ASC |
1.The code is copied from DQL10_sum_not_null .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.The last row Total: 111739.79999999999 is to be fixed in the next version |
DQL50_sum_not_null_last_row_ASC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name ASC 5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7 |
1.The code is copied from DQL40_sum_not_null_last_row_ASC .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.To fix Total:111739.79999999999 |
DQL60_sum_not_null_last_row_DESC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique ____2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name DESC 5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7 |
1.The code is copied from DQL50_sum_not_null_last_row_ASC .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.To fix Total:111739.79999999999 |
Summary_Examples
2.2. Examples
2.2.1. javascript
```javascript
let nResult = null + 10; //=>10
```
2.2.2. Inline DQL
ng_Result=`=null + 10` //=>No implementation found for 'null + number'
nResult= `=default(null, 0) + 10` //=>10
2.2.3. Summary
- Use
default
function to transformnull
into zero within dataview code block
3. Input
Summary
3.1.1. Requirements
- Suppose that there are many files located at the path
100_Project/02_dataview/Q01_Sum/Q01_test_data
. - Suppose that the filenames including
dic_
are to be taken into consideration. - To sum up a number, null, or a list of non-groupBy data with skipping the null value
> The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` must be presented in the current note.
- To sum up a number, null, or a list of groupBy data with skipping the null value
> The `dic_20220802` and `dic_20220907` must be presented in the current note.
3.2. report files
3.2.1. 00_report
- filename :
zzz_2018_Year_Total
> - The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC and DQL50_sum_not_null_last_row_ASC.
> - The file is unique in an Obsidian vault.
---
cash: 44703.1
card: 67036.7
---
### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC and DQL50_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.
- filename :
____2018_Year_Total
> - The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC.
> - The file is unique in an Obsidian vault.
---
cash: 44703.1
card: 67036.7
---
### ____2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL60_sum_not_null_last_row_DESC.
- The file is unique in an Obsidian vault.
3.3. dictionary files
- The filenames in the
Q01_test_data
folder match the/^dic_20220[2-7]0[27]$/
or/^(dic_20220802)|(dic_20220907)$/
Regular Expression. - The
file.mtime
of each file is equal tofile.ctime
+dur(2 days)
. - For example, the
file.ctime
of thedic_20220302
is2022-03-02T19:30:50
. Thefile.mtime
of thedic_20220302
is2022-03-03T19:30:50
. - The files are used primarily in testing and debugging with the
sum
function.
3.3.1. folder: 02_list
- filename :
dic_20220202_YAML
---
cash: [10, 12]
card: [15, 17]
---
- filename :
dic_20220207_DVIF
cash:: 20, 22
card:: 25, 27
3.3.2. folder: 03_null_or_undefined
- filename :
dic_20220302
cash::
card::
- filename :
dic_20220307
cash::
> dic_20220307
> cash => null(dataview_v0.5.36);| cash => undefined(dataview_v0.4.26)
> card => undefined(dataview_v0.5.36)
3.3.3. folder: 04_number
- filename :
dic_20220402
cash:: 202
card:: 302
- filename :
dic_20220407
cash:: 207
card:: 307
3.3.4. folder: 05_number
- filename :
dic_20220502
cash:: 2002
card:: 3002
- filename :
dic_20220507
cash:: 2007
card:: 3007
3.3.5. folder: 06_number_or_list
- filename :
dic_20220602
cash:: 10001, 10001
card:: 30002
- filename :
dic_20220607
cash:: 20007
card:: 12000, 18007
3.3.6. folder: 07_list_or_null
- filename :
dic_20220702
cash:: 101, 101
card::
- filename :
dic_20220707
cash::
card:: 120, 187
3.3.7. folder: 08_number_or_null
- filename :
dic_20220802
cash:: 10.1
card::
3.3.8. folder: 09_null_or_number
- filename :
dic_20220907
cash::
card:: 18.7
4. DQL10_sum_not_null:Final
Summary
4.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL10_sum_not_null | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card | It is finished. |
4.2. Notes
Purpose:To sum up a number, null, or a list of non-groupBy data with skipping the null value
```ad-info
dic_20220702
cash:: 101, 101
card::
```
```ad-info
dic_20220707
cash::
card:: 120, 187
```
```ad-info
dic_20220802
cash:: 10.1
card::
```
```ad-info
dic_20220907
cash::
card:: 18.7
```
```ad-success
1.The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` are presented in the current note.
Longer Codes:
- Left : `sum(cash)` ==>`choice(cash != null, sum(cash), 0)`
- Right: `sum(card)` ==>`choice(card != null, sum(card), 0)`
```
```ad-success
1.The `dic_20220702`, `dic_20220707`,`dic_20220802` and `dic_20220907` are presented in the current note.
Shorter Codes:
- Left : `sum(cash)` ==>`sum(default(cash, 0))`
- Right: `sum(card)` ==>`sum(default(card, 0))`
```
```ad-bug
1.The `dic_20220702`, `dic_20220707`, `dic_20220802` and `dic_20220907` are not presented in the current note where one DVIF field is null for us in order to sum up two fields such as cash and card by using `sum(cash) + sum(card)`.
2.The reason is that no implementation is found for 'null + number'.
alias: Left + Right AS "Total",
- `sum(cash) + sum(card)` AS "Total"
```
4.3. Code DQL10_sum_not_null
Summary_code
title: DQL10_sum_not_null => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
file.link AS "File",
typeof(cash) AS "T_Cash",
cash AS "Cash",
card AS "Card",
sum(default(cash, 0)) as "sum_cash",
sum(default(card, 0)) as "sum_card",
sum(default(cash, 0)) + sum(default(card, 0)) AS "Total"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```
4.3.1. Screenshots(DQL10)
5. DQL20_debug_sum_cash_groupBy
Summary
5.1. Main DQLs
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL20_debug_sum_cash_groupBy | a number, null, or a list | yes | 1.To sum up one field such as cash 2.step by step |
5.2. Examples
a_Result11=`=map([10, 20, 30], (e) => e + 6)` //=>16, 26, 36
a_Result11=`=map(list(list(1, 2, 3)), (e) => sum(e))` //=>6
a_Result01=`=filter([10,20,30], (e) => e >= 16)`
//=>20, 30
5.3. Notes
Purpose:To sum up a number, null, or a list of groupBy data with skipping the null value
```ad-info
dic_20220702
cash:: 101, 101
card::
```
```ad-info
dic_20220707
cash::
card:: 120, 187
```
```ad-success
1.dic_20220702 and dic_20220707 are presented in the current note where one field is null and the other is a list.
-
`
sum(
map(rows.cash, (e) =>
sum(default(e, 0))
)
)
` AS "sum_cash"
```
```ad-bug
1.dic_20220702 and dic_20220707 are not presented in the current note where one field is null and the other is a list.
2.The reason is that no implementation is found for 'null + number'.
- `sum(map(rows.cash, (e) => sum(e)))` AS "sum_cash",
```
5.4. Code DQL20_debug_sum_cash_groupBy
Summary_code
title: DQL20_debug_sum_cash_groupBy =>1.To sum up one field such as cash(type: a number, null, or a list) 2.groupBy data 3.step by step
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
G_m AS "G_m",
typeof(rows.cash) AS "T_r_cash",
map(rows.cash, (e) => sum(default(e, 0))) AS "S1_map_r_cash",
sum(map(rows.cash, (e) => sum(default(e, 0)))) AS "S2_sum_r_cash",
rows.cash AS "Cash_item"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
SORT file.name ASC
GROUP BY file.day.month AS G_m
```
5.4.1. Screenshots(DQL20)
6. DQL30_sum_GROUP_BY_G_month :Final
Summary
6.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL30_sum_GROUP_BY_G_month | a number, null, or a list | yes | 1.To sum up one or two fields such as cash and card | It is finished. |
6.2. Notes: Longer codes as a reference
Purpose:To sum up a number, null, or a list of groupBy data with skipping the null value
```ad-info
dic_20220802
cash:: 10.1
card::
```
```ad-info
dic_20220907
cash::
card:: 18.7
```
```ad-success
1.dic_20220802 and dic_20220907 are presented in the current note where one field is null and the other is a number.
Longer Codes:
- Left : `Left` ==>`default(Left, 0)`
- Right: `Right` ==>`default(Right, 0)`
```
```ad-bug
1.dic_20220802 and dic_20220907 are not presented in the current note where one field is null and the other is a number.
2.The reason is that no implementation is found for 'null + number'.
Longer Codes:
alias: Left + Right AS "Total",
sum(map(filter(rows.cash, (e) => e != null), (m) => sum(m))) + sum(map(filter(rows.card, (e) => e != null), (m) => sum(m))) AS "Total",
```
6.3. Code DQL30_sum_GROUP_BY_G_month
Summary_code
title: DQL30_sum_GROUP_BY_G_month =>1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.groupBy data
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
G_month AS "G_month",
sum(map(rows.cash, (e) => sum(default(e, 0)))) AS "sum_cash",
sum(map(rows.card, (e) => sum(default(e, 0)))) AS "sum_card",
sum(map(rows.cash, (e) => sum(default(e, 0)))) +
sum(map(rows.card, (e) => sum(default(e, 0)))) AS "Total",
rows.cash AS "Cash_item",
rows.card AS "Card_item"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE contains(file.name, "dic_")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
GROUP BY file.day.month AS G_month
```
6.3.1. Screenshots(DQL30)
7. DVJS10_sum_not_null_last_row
Summary
7.1. Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS10_sum_not_null_last_row | 1.a number ,null, a list | no | 1.get i_Total_cash 2.get i_Total_card 3.get i_Total 4.To display them as a table |
7.2. Code DVJS10_sum_not_null_last_row
Summary_code
title: DVJS10_sum_not_null_last_row =>1.get i_Total_cash 2.get i_Total_card 3.get i_Total 4.To display them as a table
collapse: close
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/Q01_test_data"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== "zzz_2018_Year_Total")
.where((page) => page.file.name !== "____2018_Year_Total")
.where((page) => page.cash !== null || page.card !== null);
// M21. get i_Total_cash: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_cash = dv.func.default(dv.func.sum(pages.cash), 0);
// M23. get i_Total_card: dv.func.sum(dv.func.default(pages.card, 0))
// #####################################################################
let i_Total_card = dv.func.default(dv.func.sum(pages.card), 0);
// M25. get i_Total: i_Total_cash + i_Total_card
// #####################################################################
let i_Total = i_Total_cash + i_Total_card;
i_Total = dv.func.round(i_Total, 1);
// M81. Output :i_Total_cash, i_Total_card, i_Total
// #####################################################################
// dv.span("i_Total_cash=" + i_Total_cash);//=>44703.1
// dv.span("<br>");
// dv.span("i_Total_card=" + i_Total_card);//=>67036.7
// dv.span("<br>");
// dv.span("i_Total=" + i_Total);//=>111739.8
// M91. TABLE :i_Total_cash, i_Total_card, i_Total
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
dv.table(
["", "cash", "card", "Total"],
[
["**Total**", i_Total_cash, i_Total_card, i_Total],
]
);
```
7.2.1. Screenshots(DVJS10)
8. DQL40_sum_not_null_last_row_ASC
Summary
8.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL40_sum_not_null_last_row_ASC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name ASC |
1.The code is copied from DQL10_sum_not_null .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.The last row Total: 111739.79999999999 is to be fixed in the next version |
8.2. Metadata: used by DQL40_sum_not_null_last_row_ASC
Summary
8.2.1. 00_report
- filename :
zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---
### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL40_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.
The DVIF content in the
zzz_2018_Year_Total
file will be replaced with inline DVJS.
8.3. Code DQL40_sum_not_null_last_row_ASC
Summary_code
title: DQL40_sum_not_null_last_row_ASC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `zzz_2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name ASC`
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
choice(file.name = "zzz_2018_Year_Total", "**Total**", file.link) AS "File",
cash AS "Cash",
card AS "Card",
sum(default(cash, 0)) as "sum_cash",
sum(default(card, 0)) as "sum_card",
sum(default(cash, 0)) + sum(default(card, 0)) AS "Total"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "zzz_2018_Year_Total")
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```
8.3.1. Screenshots(DQL40)
9. DQL50_sum_not_null_last_row_ASC
Summary
9.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL50_sum_not_null_last_row_ASC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique zzz_2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name ASC 5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7 |
1.The code is copied from DQL40_sum_not_null_last_row_ASC .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.To fix Total:111739.79999999999 |
9.2. Metadata: used by DQL50_sum_not_null_last_row_ASC
Summary
9.2.1. 00_report
- filename :
zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---
### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL50_sum_not_null_last_row_ASC.
- The file is unique in an Obsidian vault.
The DVIF content in the
zzz_2018_Year_Total
file will be replaced with inline DVJS.
9.2.2. Notes: a serious bug
```
choice(file.name = "zzz_2018_Year_Total", round([[zzz_2018_Year_Total]].cash +[[zzz_2018_Year_Total]].card, 1) , sum(default(cash, 0)) + sum(default(card, 0))) AS "Total"
```
9.3. Code DQL50_sum_not_null_last_row_ASC
Summary_code
title: DQL50_sum_not_null_last_row_ASC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `zzz_2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name ASC` 6.To fix Total:111739.79999999999 which is from the expression `44703.1 + 67036.7`
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
choice(file.name = "zzz_2018_Year_Total", "**Total**", file.link) AS "File",
cash AS "Cash",
card AS "Card",
sum(default(cash, 0)) as "sum_cash",
sum(default(card, 0)) as "sum_card",
round(sum(default(cash, 0)) + sum(default(card, 0)), 1) AS "Total"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "zzz_2018_Year_Total")
WHERE file.name != "____2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name ASC
```
9.3.1. Screenshots(DQL50)
10. DQL60_sum_not_null_last_row_DESC
Summary
10.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL60_sum_not_null_last_row_DESC | a number, null, or a list | no | 1.To sum up one or two fields such as cash and card 2.To add Total in the last row 3.The unique ____2018_Year_Total file in the source folder includes the same DVIF fields, where the inline DVJS is.4.Require SORT file.name DESC 5.To fix Total:111739.79999999999 which is from the expression 44703.1 + 67036.7 |
1.The code is copied from DQL50_sum_not_null_last_row_ASC .2.To add Total in the last row 3.SORT file.name ASC: To use zzz_2018_Year_Total 4.SORT file.name DESC:To use _____2018_Year_Total 5.To fix Total:111739.79999999999 |
10.2. Metadata: used by DQL60_sum_not_null_last_row_DESC
Summary
10.2.1. 00_report
- filename :
zzz_2018_Year_Total
---
cash: 44703.1
card: 67036.7
---
### zzz_2018_Year_Total
- The report file is ONLY used as the last row of a table by DQL60_sum_not_null_last_row_DESC.
- The file is unique in an Obsidian vault.
The DVIF content in the
zzz_2018_Year_Total
file will be replaced with inline DVJS.
10.2.2. Notes: a serious bug
```
choice(file.name = "____2018_Year_Total", round([[____2018_Year_Total]].cash +[[____2018_Year_Total]].card, 1) , sum(default(cash, 0)) + sum(default(card, 0))) AS "Total"
```
10.3. Code DQL60_sum_not_null_last_row_DESC
Summary_code
title: DQL60_sum_not_null_last_row_DESC => 1.To sum up one or two fields such as cash and card(type: a number, null, or a list) 2.non-groupBy data 3.To add Total in the last row 4.The unique `____2018_Year_Total` file in the source folder includes the same DVIF fields, where the inline DVJS is. 5.Require `SORT file.name DESC` 6.To fix Total:111739.79999999999 which is from the expression `44703.1 + 67036.7`
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
choice(file.name = "____2018_Year_Total", "**Total**", file.link) AS "File",
cash AS "Cash",
card AS "Card",
sum(default(cash, 0)) as "sum_cash",
sum(default(card, 0)) as "sum_card",
round(sum(default(cash, 0)) + sum(default(card, 0)), 1) AS "Total"
FROM "100_Project/02_dataview/Q01_Sum/Q01_test_data"
WHERE (contains(file.name, "dic_") OR file.name = "____2018_Year_Total")
WHERE file.name != "zzz_2018_Year_Total"
WHERE file.name != this.file.name
WHERE cash != null OR card != null
SORT file.name DESC
```
10.3.1. Screenshots(DQL60)
11. Conclusion
Summary
- According to the code above, there is a summary as follows.
Field Name | Data type | Group By | Purpose | DQL Expression | DVJS Expression |
---|---|---|---|---|---|
cash | a number, null, or a list of numbers | no | To sum up one field such as cash | sum(default(cash, 0)) AS “sum_cash” |
let i_Total_cash = dv.func.default(dv.func.sum(pages.cash), 0); |
cash | a number, null, or a list of numbers | yes | To sum up one field such as cash | sum(map(rows.cash, (e) => sum(default(e, 0)))) AS “sum_cash” |
Topic : S02
Summary
- How to sum up a number or a list of numbers (non-groupBy)(or groupBy) with skipping the null value? PS.To sum up one field
Test
Summary
- dataview: v0.5.46
2. Overview
Summary
2.1. Main DQL or DVJS
- The fowllowng table presents the main DQL, DVJS or inline DVJS in the Solutions.
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL10_sum_number_or_list | exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise | |
DQL20_sum_number_or_list_GROUP_BY | exercise :1.a number 2.a list of numbers |
yes | 1.To sum up one field such as exercise 2.group.key to fullname_of_month |
|
DVJS10_sum_number_or_list _and_TABLE_last_row_Total_seperately |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise 2.To display it as a table 3.To get i_Total_exercise 4.To display it as a table and hide the file count in the dataview table |
The code =DQL10_sum_number_or_list + DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
DVJS15_sum_number_or_list _and_TABLE_last_row_Total_merged |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise 2.To get i_Total_exercise 3.To display it as a table |
The code =DVJS10_sum_number_or_list _and_TABLE_last_row_Total_seperately |
DVJS20_sum_number_or_list_groupBy _and_TABLE_last_row_Total_seperately |
exercise :1.a number 2.a list of numbers |
yes | 1.To sum up one field such as exercise 2.group.key to fullname_of_month 3.To display it as a table 4.To get i_Total_exercise 5.To display it as a table and hide the file count in the dataview table |
The code =DQL20_sum_number_or_list_GROUP_BY + DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise in each file 2.To display it as a table and hide the file count in the dataview table |
|
inDVJS10_sum_number_or_list_Total | exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise in each file 2.To display it as a value 3.Inline DVJS |
The code is fromDVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
Summary_Examples
2.2. SUM
2.2.1. SUM = (reduce(list, (a, b) => a + b))
2.2.1.1. a list of non-groupBy data = 1D array
Total_list=`=reduce([1,2,3], (a, b) => a + b)`//=>6
2.2.1.2. a list of groupBy data = 2D array = Array of arrays = AoA
a_List=`=reduce([[1],[2],[3]], (a, b) => a + b)`//=>1, 2, 3
Total_AoA=`=reduce(reduce([[1],[2],[3]], (a, b) => a + b),(a, b) => a + b)`//=>6
2.2.2. SUM
2.2.2.1. a list of non-groupBy data = 1D array
Total_list=`=sum([1,2,3])`//=>6
2.2.2.2. a list of groupBy data = 2D array = Array of arrays = AoA
a_List=`=sum([[1],[2],[3]])`//=>1, 2, 3
Total_AoA=`=sum(sum([[1],[2],[3]]))`//=>6
2.3. Examples
2.3.1. javascript
```javascript
let nResult = null + 10; //=>10
```
2.3.2. Inline DQL
ng_Result=`=null + 10` //=>No implementation found for 'null + number'
2.3.3. Use default
to transform null
into zero before using sum
nResult= `=default(null, 0) + 10` //=>10
2.3.4. Use number
to transform string
into number
before using sum
nResult= `=sum(number("5000"))` //=>5000
2.3.5. Use split
to get /[0-9]+/ from string
and use number
to transform it before using sum
// #### meta data ####
Price:: Note01 - 5000
Price:: Note02 - 7000
// #### meta data ####
2.3.5.1. Test
Price=`=split("Note01 - 5000", " - ")[1]`//=>5000
Price=`=split("Note01 - 7000", " - ")[1]`//=>7000
2.3.5.2. OK result
total_Price:: **TOTAL** - `=sum(map(this.Price, (e) => number(split(e, " - ")[1])))`//=>TOTAL - 12000
2.3.5.3. NG result
total_Price:: **TOTAL** = `=sum(number(this.Price))`//=>TOTAL = 3
2.3.6. Summary
- Make sure the input data type is
number
beforesum
.
3. Input
Summary
3.1. dictionary files:
- Location: “100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02”
3.1.1. F02_number_or_list
- filename :
dic_20220202_exercise
---
exercise: 60
---
- filename :
dic_20220207_exercise
---
exercise: [60, 60]
---
3.1.2. A03__list
- filename :
dic_20220302_exercise
---
exercise: [60, 35]
---
- filename :
dic_20220307_exercise
---
exercise: [56, 22]
---
4. DQL10_sum_number_or_list
Summary
4.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL10_sum_number_or_list | exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise |
4.2. Code DQL10_sum_number_or_list
Summary_code
title: DQL10_sum_number_or_list => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
file.link AS "File",
file.name AS "file.name",
sum(exercise) as "sum_exercise",
exercise AS "exercise"
FROM "100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"
WHERE contains(file.name, "dic_")
WHERE file.name != this.file.name
WHERE exercise != null
SORT file.name ASC
```
4.2.1. Screenshots(DQL10)
5. DQL20_sum_number_or_list_GROUP_BY
Summary
5.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DQL20_sum_number_or_list_GROUP_BY | exercise :1.a number 2.a list of numbers |
yes | 1.To sum up one field such as exercise 2.group.key to fullname_of_month |
5.2. Code DQL20_sum_number_or_list_GROUP_BY
Summary_code
title: DQL20_sum_number_or_list_GROUP_BY =>1.To sum up one field such as exercise(type: a number or a list of numbers) 2.groupBy data 3.group.key to fullname_of_month
collapse: open
icon:
color:
```dataview
TABLE WITHOUT ID
FG_month AS "G_month",
rows.file.link AS "File",
sum(map(rows.exercise, (e) => sum(e))) AS "sum_exercise",
rows.exercise AS "exercise"
FROM "100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"
WHERE contains(file.name, "dic_")
WHERE file.name != this.file.name
WHERE exercise != null
WHERE file.day.year = 2022
SORT file.name ASC
GROUP BY file.day.month AS G_m
FLATTEN {
"1": "January",
"2": "February",
"3": "March",
"4": "April",
"5": "May",
"6": "June",
"7": "July",
"8": "August",
"9": "September",
"10": "October",
"11": "November",
"12": "December"
}[string(G_m)] AS "FG_month"
```
5.2.1. Screenshots(DQL20)
6. DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately
Summary
6.1. Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS10_sum_number_or_list _and_TABLE_last_row_Total_seperately |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise 2.To display it as a table 3.To get i_Total_exercise 4.To display it as a table and hide the file count in the dataview table |
The code =DQL10_sum_number_or_list + DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
6.2. Code DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately
Summary_code
title: DVJS10_sum_number_or_list_and_TABLE_last_row_Total_seperately => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data 3.To display it as a table 4.To get i_Total_exercise 5.To display it as a table and hide the file count in the dataview table
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise !== null)
.sort((page) => page.file.name, "asc");
// ### M21.Output : pages
// #####################################################################
dv.header(2, "M21.Exercise Report in 2022");
dv.table(
["N", "File", "file.name", "sum_exercise", "exercise"],
pages.map((page, index) => [
index + 1,
page.file.link ,
page.file.name,
dv.func.sum(page.exercise),
page.exercise,
])
);
// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// M51. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353
// M53. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
dv.table(
["", "exercise", "Total"],
[
["**Total**", i_Total_exercise, i_Total_exercise],
]
);
// M61. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = 2;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";
```
6.2.1. Screenshots(DVJS10)
7. DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged
Summary
7.1. Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS15_sum_number_or_list _and_TABLE_last_row_Total_merged |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise 2.To get i_Total_exercise 3.To display it as a table |
The code =DVJS10_sum_number_or_list _and_TABLE_last_row_Total_seperately |
7.2. Code DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged
Summary_code
title: DVJS15_sum_number_or_list_and_TABLE_last_row_Total_merged => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.non-groupBy data 3.To get i_Total_exercise 4.To display it as a table
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise !== null)
.sort((page) => page.file.name, "asc");
// M21. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// ### M31.define : aoa_drinks
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
// ["N", "File", "file.name", "sum_exercise", "exercise"]
let aoa_drinks = pages.map((page, index) => [
index + 1,
page.file.link ,
page.file.name,
dv.func.sum(page.exercise),
page.exercise,
]);
// ### M33.update aoa_drinks:push the_lat_row_Total
// #####################################################################
// ["N", "File", "file.name", "sum_exercise", "exercise"]
// aoa_drinks.push([
// "**Total**",
// "",
// "",
// i_Total_exercise,
// i_Total_exercise,
// ]);
aoa_drinks["values"][aoa_drinks.length] = [
"**Total**",
"",
"",
i_Total_exercise,
i_Total_exercise,
];
// M51. TABLE :aoa_drinks
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
dv.header(2, "M51.Exercise Report in 2022");
dv.table(["N", "File", "file.name", "sum_exercise", "exercise"], aoa_drinks);
```
7.2.1. Screenshots(DVJS15)
8. DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately
Summary
8.1. Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS20_sum_number_or_list_groupBy _and_TABLE_last_row_Total_seperately |
exercise :1.a number 2.a list of numbers |
yes | 1.To sum up one field such as exercise 2.group.key to fullname_of_month 3.To display it as a table 4.To get i_Total_exercise 5.To display it as a table and hide the file count in the dataview table |
The code =DQL20_sum_number_or_list_GROUP_BY + DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
8.2. Code DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately
Summary_code
title: DVJS20_sum_number_or_list_groupBy_and_TABLE_last_row_Total_seperately => 1.To sum up one field such as exercise(type: a number or a list of numbers) 2.groupBy data 3.group.key to fullname_of_month 4.To display it as a table 5.To get i_Total_exercise 6.To display it as a table and hide the file count in the dataview table
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise !== null)
.where((page) => page.file.day.year === 2022)
.sort((page) => page.file.name, "asc");
// M13. GROUP BY file.day.month:
// #####################################################################
dv.header(2, "M13.Exercise Report groupBy Month in 2022");
for (let group of pages.groupBy((page) => page.file.day.month)) {
// M13.FR10 transform the group.key into the fullname_of_month:
// #####################################################################
//dv.header(3, group.key);
dv.header(3,
{
"1": "January",
"2": "February",
"3": "March",
"4": "April",
"5": "May",
"6": "June",
"7": "July",
"8": "August",
"9": "September",
"10": "October",
"11": "November",
"12": "December"
}[group.key]
);
// M13.FR12 TABLE:
// #####################################################################
dv.table(
["N", "File", "sum_exercise", "exercise"],
group.rows
.sort((k) => k.file.name, "desc")
.map((k, index) => [
index + 1,
k.file.link,
dv.func.sum(k.exercise),
k["exercise"],
])
);
}
// M41. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// M51. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353
// M53. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
dv.table(
["", "exercise", "Total"],
[
["**Total**", i_Total_exercise, i_Total_exercise],
]
);
// M61. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let a_unique_months = pages.file.day.month.where((e) => !(this[e] = e in this));
//dv.span(a_unique_months);//=>2,3
// M63. get te counts of unique file.day.month : page.file.day.month
// #####################################################################
let i_counts_of_month = a_unique_months.length;
//dv.span(i_counts_of_month);//=>2
// M65. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = i_counts_of_month + 1 ;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";
```
8.2.1. Screenshots(DVJS20)
9. DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY
Summary
9.1. Main DQL
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise in each file 2.To display it as a table and hide the file count in the dataview table |
9.2. Code DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY
Summary_code
title: DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY => 1.To sum up one field such as exercise(type: a number or a list of numbers) in each file 2.non-groupBy data 3.To display it as a table and hide the file count in the dataview table
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise !== null)
.where((page) => page.file.day.year === 2022);
// M21. get i_Total_exercise: dv.func.sum(dv.func.default(pages.cash, 0))
// #####################################################################
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
// M31. Output : i_Total_exercise
// #####################################################################
//dv.span("i_Total_exercise=" + i_Total_exercise);//=>i_Total_exercise=353
// M41. TABLE :i_Total_exercise, i_Total_exercise
// #####################################################################
// dv.table(
// ["Name", "Price", "Caffeine Content"],
// [
// ["Black Coffee", 120, 300],
// ["Green Tea", 100, 200],
// ["Apple Juice", 110, 0],
// ["Iced Chocolate", 130, 0],
// ["Hot Chocolate", 105, 6],
// ]
// );
dv.header(2, "M41.Exercise Report in 2022");
dv.table(
["", "exercise", "Total"],
[
["**Total**", i_Total_exercise, i_Total_exercise],
]
);
// M51. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = 1;
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[the_nth_of_table-1].style.visibility = "hidden";
```
9.2.1. Screenshots(DVJS30)
10. inDVJS10_sum_number_or_list_Total
Summary
10.1. Main Inline DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
inDVJS10_sum_number_or_list_Total | exercise :1.a number 2.a list of numbers |
no | 1.To sum up one field such as exercise in each file 2.To display it as a value 3.Inline DVJS |
The code is fromDVJS30_sum_number_or_list _and_TABLE_last_row_ONLY |
10.2. Notes
Notes:
1.copy step M11 and M21 from DVJS30_sum_number_or_list_and_TABLE_last_row_ONLY into an Inline DVJS expression
2.Remove the comments .
3.Adddv.span(i_Total_exercise);
in the last row in the Inline DVJS expression.
4.Remove the blank lines.
5.An Inline DVJS is finished!
10.3. Code inDVJS10_sum_number_or_list_Total
Summary_code
Total=`$=
let pages = dv .pages('"100_Project/02_dataview/Q01_Sum/20220725_Sum_02/Q01_test_data02"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise !== null)
.where((page) => page.file.day.year === 2022);
let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0);
dv.span(i_Total_exercise);
`
10.3.1. Screenshots(inDVJS10)
//=>353
13. Conclusion
Summary
13.1. Sum
- According to the codes above, there is a summary as follows.
Field Name | Data type | Group By | Purpose | DQL Expression | DVJS Expression (Table vs. last_row_Total) | Reamrk |
---|---|---|---|---|---|---|
exercise | 1.a number 2.a list of numbers |
no | To sum up one field | sum(exercise) AS “sum_exercise” |
1.M21: (not using GROUP BY )dv.func.sum(page.exercise), 2.M41: (not using GROUP BY )let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0); |
1.S02 > DQL10 2.S2 > DVJS10 |
exercise | 1.a number 2.a list of numbers |
yes (once) |
To sum up one field | sum(map(rows.exercise, (e) => sum(e))) AS “sum_list_G1_11” |
1.M13.FR12: (using GROUP BY once)dv.func.sum(k.exercise), 2.M41: (not using GROUP BY )let i_Total_exercise = dv.func.default(dv.func.sum(pages.exercise), 0); |
1.S02 > DQL20 2.S02 > DVJS20 |
13.2. To group by the data (once)
Field Name | Data type | Group By | Purpose | DQL Expression |
---|---|---|---|---|
exercise | 1.a number 2.a list of numbers |
yes | To groupBy year | GROUP BY file.day.year AS G_year |
exercise | 1.a number 2.a list of numbers |
yes | To groupBy month (For same year only) |
GROUP BY file.day.month AS G_month |
exercise | 1.a number 2.a list of numbers |
yes | To groupBy calendarweek | FLATTEN dateformat(file.day, “yyyy-WW”) AS F_calendarweek GROUP BY F_calendarweek AS GF_calendarweek |
14. Reference
Summary
Topic : S03
Summary
- How to sum up one field such as
exercise
(type: a number or a list of numbers) without/with using groupBy? - How to display the result as a bar chart? (Require obsidian-charts v3.6.2)
Test
Summary
- dataview v0.5.46
- obsidian-charts v3.6.2
Input
Summary
dictionary files:
- Location: “100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03”
03_number
- filename :
dic_20170301
---
Date: 2017-03-01
exercise: 60
---
- filename :
dic_20170306
---
Date: 2017-03-06
exercise: 120
---
04_list
- filename :
dic_20170401
---
Date: 2017-04-01
exercise: [60, 35]
---
- filename :
dic_20170406
---
Date: 2017-04-06
exercise: [56, 22]
---
08_null
- filename :
dic_20170801
---
Date: 2017-08-01
exercise:
---
09_undefined
- filename :
dic_20170901
---
Date: 2017-09-01
---
DVJS10_sum_number_or_list_and_bar_chart
Summary
Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS10_sum_number_or_list _and_bar_chart |
exercise :1.a number 2.a list of numbers |
no |
1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise 3.To display it as a bar chart |
The code is from DVJS10_sum_number_or_list _and_TABLE_last_row_Total_seperately in the following topic. - 20220725_Solutions |
Code DVJS10_sum_number_or_list_and_bar_chart
Summary_code
title: DVJS10_sum_number_or_list_and_bar_chart => 1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise(type: a number or a list of numbers) 3.non-groupBy data 4.To display it as a bar chart
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise)
.where((page) => page.file.day.year === 2017)
.sort((page) => page.file.name, "asc");
// M21. define s_label_of_chart for the chart: excel chart title
// ### Used by obsidian-charts v3.6.2 ###
// #######################################################
let s_label_of_chart = "M41.Exercise Report in 2017";
// M23. define a_labels_of_chart for the chart: excel Category X-axis
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
// ["N", "File", "file.name", "sum_exercise", "exercise"],
let a_labels_of_chart = pages.file.day
.map((e) => e.toFormat("yyyy-MM-dd"))
.array();
// #######################################################
// #######################################################
// M23.DEB10 Debug Output: a_labels_of_chart
// #######################################################
// #######################################################
// M23.DEB12 Debug Output: a_labels_of_chart: before using `.array()`
// #######################################################
// {
// "values": [
// "2022-02-02",
// "2022-02-07",
// "2022-03-02",
// "2022-03-07"
// ],
// "length": 4
// }
// M23.DEB14 Debug Output: a_labels_of_chart: after using `.array()`
// #######################################################
// The following is the content of the a_labels_of_chart.
// [
// "2022-02-02",
// "2022-02-07",
// "2022-03-02",
// "2022-03-07"
// ]
// M25.define a_data_of_chart for the chart: excel data series
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
let a_data_of_chart = pages.map((page) => dv.func.sum(page.exercise)).array();
// #######################################################
// #######################################################
// M25.DEB10 Debug Output: a_data_of_chart
// #######################################################
// #######################################################
// M25.DEB13 Debug Output: a_data_of_chart: before using `.array()`
// #######################################################
// {
// "values": [
// 60,
// 120,
// 95,
// 78
// ],
// "length": 4
// }
// M25.DEB15 Debug Output: a_data_of_chart: after using `.array()`
// #######################################################
// [
// 60,
// 120,
// 95,
// 78
// ]
// M41.setup the data for the chart: No modification required
// Use PicPick:Color Picker to get rgb: [Color Picker](https://picpick.app/en/)
// #######################################################
const chartData = {
labels: a_labels_of_chart,
datasets: [
{
label: s_label_of_chart,
data: a_data_of_chart,
backgroundColor: [
"rgba(230, 142, 147, 0.786)", //01:orange
"rgba(123, 194, 200, 0.786)", //02:pink greenish
"rgba(202, 145, 212, 0.786)", //03:pink purple
"rgba(116, 171, 219, 0.786)", //04:pink blue
"rgba(229, 168, 116, 0.786)", //05:pink orange
"rgba(104, 103, 172, 0.786)", //06:pink purple 02
"rgba(81, 196, 211, 0.786)", //07:pink greenish 02
],
borderColor: [
"rgba(230, 142, 147, 1.0)", //01:orange
"rgba(123, 194, 200, 1.0)", //02:pink greenish
"rgba(202, 145, 212, 1.0)", //03:pink purple
"rgba(116, 171, 219, 1.0)", //04:pink blue
"rgba(229, 168, 116, 1.0)", //05:pink orange
"rgba(104, 103, 172, 1.0)", //06:pink purple 02
"rgba(81, 196, 211, 1.0)", //07:pink greenish 02
],
borderWidth: 2,
},
],
};
// M43. configure the chart: No modification required
// #######################################################
const config = {
type: "bar",
data: chartData,
};
// M45. render the chart: No modification required
// #######################################################
window.renderChart(config, this.container);
// M91.TABLE : pages
// #####################################################################
dv.header(2, "M91.Exercise Report in 2017");
dv.table(
["N", "File", "sum_exercise", "exercise"],
pages.map((page, index) => [
index + 1,
page.file.link ,
dv.func.sum(page.exercise),
page.exercise,
])
);
```
Screenshots(DVJS10)
Part 1/2 :
Part 2/2 :
DVJS20_sum_number_or_list_groupBy_and_bar_chart
Summary
Main DVJS
Code Name | Data type | Group By | Purposes | Remark |
---|---|---|---|---|
DVJS20_sum_number_or_list _groupBy_and_bar_chart |
exercise :1.a number 2.a list of numbers |
yes |
1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise 3.To display it as a bar chart |
The code is based on the DVJS20_sum_number_or_list _groupBy_and_TABLE _last_row_Total_seperately in the following topic. - 20220725_Solutions |
Code DVJS20_sum_number_or_list_groupBy_and_bar_chart
Summary_code
title: DVJS20_sum_number_or_list_groupBy_and_bar_chart => 1.Require obsidian-charts v3.6.2 2.To sum up one field such as exercise(type: a number or a list of numbers) 3.groupBy data 4.To display it as a bar chart
collapse: open
icon:
color:
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
.pages('"100_Project/02_dataview/Q01_Sum/20220731_Sum_03/Q01_test_data03"')
.where((page) => dv.func.contains(page.file.name, "dic_"))
.where((page) => page.file.name !== dv.current().file.name)
.where((page) => page.exercise)
.where((page) => page.file.day.year === 2017)
.sort((page) => page.file.name, "asc");
// M20. get te counts of unique month: page.file.month ( year = 2022 )
// #####################################################################
let a_unique_months = pages.file.day.month
.where((e) => !(this[e] = e in this))
.sort();
//=>[2, 3]
// M21. define s_label_of_chart for the chart: excel chart title
// ### Used by obsidian-charts v3.6.2 ###
// #######################################################
let s_label_of_chart = "M41.Exercise Report in 2017";
// M23. define a_labels_of_chart for the chart: excel Category X-axis
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
// [page.file.day.month]//=>[2, 3]=> ["February", "March"]
let a_labels_of_chart = [];
a_labels_of_chart = a_unique_months.map(
(e) =>
dv.func.object(
"1","January",
"2","February",
"3","March",
"4","April",
"5","May",
"6","June",
"7","July",
"8","August",
"9","September",
"10","October",
"11","November",
"12","December"
)[dv.func.string(e)]
);
// M24. Define h_month_exercise_YYYYMM: {M: sum(exercise)} ( year = 2022 )
// #####################################################################
let h_month_exercise_YYYYMM = {};
for (let page of pages) {
let s_YYYYMM = page.file.day.toFormat("M");
if (s_YYYYMM in h_month_exercise_YYYYMM) {
h_month_exercise_YYYYMM[s_YYYYMM] += dv.func.sum(page.exercise);
} else {
h_month_exercise_YYYYMM[s_YYYYMM] = dv.func.sum(page.exercise);
}
}
// M24.DEB01 Debug Output: h_month_exercise_YYYYMM
// ######################################################
// The following is the content of the h_month_exercise_YYYYMM.
// {
// "2": 180,
// "3": 173
// }
// M25.define a_data_of_chart for the chart: excel data series
// ### Used by obsidian-charts v3.6.2 ###
// use .array() to transform a dataview array into a JavaScript array
// dv_array.array() = dv_array.values = dv_array["values"]
// #######################################################
//let a_data_of_chart = pages.map((page) => dv.func.sum(page.exercise)).array();
let a_data_of_chart = [];
a_data_of_chart = a_unique_months.map((e) => h_month_exercise_YYYYMM[dv.func.string(e)]).array();
// M25.DEB01 Debug Output: a_data_of_chart
// ######################################################
// The following is the content of the a_data_of_chart.
// [
// 180,
// 173
// ]
// M41.setup the data for the chart: No modification required
// Use PicPick:Color Picker to get rgb: [Color Picker](https://picpick.app/en/)
// #######################################################
const chartData = {
labels: a_labels_of_chart,
datasets: [
{
label: s_label_of_chart,
data: a_data_of_chart,
backgroundColor: [
"rgba(230, 142, 147, 0.786)", //01:orange
"rgba(123, 194, 200, 0.786)", //02:pink greenish
"rgba(202, 145, 212, 0.786)", //03:pink purple
"rgba(116, 171, 219, 0.786)", //04:pink blue
"rgba(229, 168, 116, 0.786)", //05:pink orange
"rgba(104, 103, 172, 0.786)", //06:pink purple 02
"rgba(81, 196, 211, 0.786)", //07:pink greenish 02
],
borderColor: [
"rgba(230, 142, 147, 1.0)", //01:orange
"rgba(123, 194, 200, 1.0)", //02:pink greenish
"rgba(202, 145, 212, 1.0)", //03:pink purple
"rgba(116, 171, 219, 1.0)", //04:pink blue
"rgba(229, 168, 116, 1.0)", //05:pink orange
"rgba(104, 103, 172, 1.0)", //06:pink purple 02
"rgba(81, 196, 211, 1.0)", //07:pink greenish 02
],
borderWidth: 2,
},
],
};
// M43. configure the chart: No modification required
// #######################################################
const config = {
type: "bar",
data: chartData,
};
// M45. render the chart: No modification required
// #######################################################
window.renderChart(config, this.container);
// M91. GROUP BY file.day.month + TABLE :
// #####################################################################
dv.header(2, "M13.Exercise Report groupBy Month in 2017");
for (let group of pages.groupBy((page) => page.file.day.month)) {
//dv.header(3, group.key);
dv.header(3,
{
"1": "January",
"2": "February",
"3": "March",
"4": "April",
"5": "May",
"6": "June",
"7": "July",
"8": "August",
"9": "September",
"10": "October",
"11": "November",
"12": "December"
}[group.key]
);
dv.table(
["N", "File", "sum_exercise", "exercise"],
group.rows
.sort((k) => k.file.name, "desc")
.map((k, index) => [
index + 1,
k.file.link,
dv.func.sum(k.exercise),
k["exercise"],
])
);
}
```
Screenshots(DVJS20)
Part 1/2 :
Part 2/2 :
Reference
Summary
As far as I can see your question hasn’t been answered yet… In case you haven’t found the solution so far, the correct syntax, I think, should be:
LIST
WHERE contains(type,"grammar")
Thank you very much for reviewing my question! I haven’t figure it out. Thank you this helped me to continue on my db!
Hi all, I’m having trouble getting the ‘Find All Direct And Indirectly Linked Pages’ example from Codeblock Examples - Dataview to work.
When I cut and paste the codeblock, adding the three ticks and using ‘dataviewjs’ all I get is a really think block with no visible content (see screenshot - this is just a sliver of the ‘edit code’ button)
On the page where the code is pasted are 2 obsidian links and 1 embedded pdf, which I would have expected to be returned in the results…
Thank you for any help or advice you may have.
This is going to be hard to investigate! Did you paste as plain text or could some extra stuff from the web have gotten included? Can you share a screenshot with the code visible (maybe switch from Live Preview to Source View)? That might make it easier for folks to help!
Thank you @scholarInTraining for your reply. I just clicked the ‘copy to clipboard’ icon in the top-right hand corner of the code block in github. Here is a screenshot of the code visible.
This is the code block from github:
There are some differences in formatting but this (I think) has more to do with the window size of the code block in Obsidian.
Thank you for posting that! Yes, you are right and I was very off-target in my guess.
There is no output because the codeblock example is missing any lines of code to actually output the data! For something very simple, try dv.table(['Note'], data.map(p => [p.file.link]));
which should give you a one-column table (column title “Note”) with the links to each of the files in the results.
Thank you @scholarInTraining. Where exactly to I put the line of code? I assumed it was the last line of code but that didn’t work. This…
produced this error…
Thanks again for your help.
You guessed correctly, but I think the original last line of code got deleted in the process. It started let data =
. That line defines data
, so its deletion is causing the error. Then my new line of code goes at the very end.