How do you group by twice in either dataview or dataviewjs?

justdoitcc · January 14, 2023, 2:27pm

Topic

Summary

How to group the data by month and group the grouped data by week in either dataview or dataviewjs?

Test

Summary

dataview: v0.5.46

Input

Summary

dictionary files:

Location: “100_Project/01_dataviewjs/01_by_example/Q29_GroupIn/Q29_test_data”

folder: R03

filename : dic_19650301_W1

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

month::  "March"
week:: 1


Cost::  3
Company:: "USA"
Method:: "cash"
Type::  
Desc::

filename : dic_19650308_W2

---
Date: 1965-03-08
---
#Project/P03

month::  "March"
week:: 2


Cost::  30
Company:: "USA"
Method:: "cash"
Type::  
Desc::

filename : dic_19650315_W3

---
Date: 1965-03-15
---
#Project/P03

month:: "March"
week:: 3


Cost::  300
Company:: "USA"
Method:: "charge"
Type::  
Desc::

filename : dic_19650316_W3P

---
Date: 1965-03-16
---
#Project/P03

month::  "March"
week:: 3


Cost::  3000
Company:: "USA"
Method:: "charge"
Type::  
Desc::

folder: A08

filename : dic_19650802_W1

---
Date: 1965-08-02
---
#Project/P08

month::  "August"
week:: 1


Cost::  8
Company:: "FRA"
Method:: "cash"
Type::  
Desc::

filename : dic_19650803_W1P

---
Date: 1965-08-03
---
#Project/P08

month::  "August"
week:: 1


Cost::  80
Company:: "FRA"
Method:: "cash"
Type::  
Desc::

filename : dic_19650816_W3

---
Date: 1965-08-16
---
#Project/P08

month::  "August"
week:: 3


Cost::  800
Company:: "FRA"
Method:: "charge"
Type::  
Desc::

filename : dic_19650830_W5

---
Date: 1965-08-30
---
#Project/P08

month::  "August"
week:: 5


Cost::  8000
Company:: "FRA"
Method:: "charge"
Type::  
Desc::

folder: D12

filename : dic_19651206_W2

---
Date: 1965-12-06
---
#Project/P12

month::  "December"
week:: 2


Cost::  12
Company:: "ITA"
Method:: "cash"
Type::  
Desc::

filename : dic_19651220_W4

---
Date: 1965-12-20
---
#Project/P12

month::  "December"
week:: 4


Cost::  120
Company:: "ITA"
Method:: "cash"
Type::  
Desc::

filename : dic_19651227_W5

---
Date: 1965-12-27
---
#Project/P12

month::  "December"
week:: 5


Cost::  1200
Company:: "ITA"
Method:: "charge"
Type::  
Desc::

filename : dic_19651228_W5P

---
Date: 1965-12-28
---
#Project/P12

month::  "December"
week:: 5


Cost::  12000
Company:: "ITA"
Method:: "charge"
Type::  
Desc::

DVJS10_groupBy_month_groupIn_week_and_LIST

Summary

Main DVJS

Code Name	Data type	Group By	Purposes	Remark
DVJS10 _groupBy_month _groupIn_week _and_LIST	`month`: a string like “January”, “February” , …, or “December” `week`: a number like 1, 2, 3, 4 or 5	yes (twice)	1.To group each `page` of the `pages` by `page.month` (Results:)(key=G1.key)(values=G1.rows) 2.To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order 3.To group each `G1.rows` of the `G1` by `G1.rows.week` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`} 4.To display the result: 4.1 To display each `G1.key`(`month`) as a heading element(H3)(“asc”) 4.2 To display each `G2.key`(`week`) as a heading element(H6)(“asc”) 4.2.1 The H6 is “Week 1”, “Week 2”, “Week 3”, “Week 4”, or “Week 5” 4.3 To display each `G2.rows`(“Cost”, “Company”, “Method”, “Link”) as a list (`Cost`:“asc”)	1.The DVJS10 is based on the DVJS20_A2_21 in the following topic. - Solutions: by Justdoitcc

Notes:

Summary

Q1: How to sort the `groups` by `G1.key`(`month`)(H3) in custom order? (DVJS10:M13+M21)

Summary_Q1

A1_11:

Original Example: A1_11

```dataviewjs
// M13. define h_month_number_of:
// Features: To transform a month into a number
// Purposes: To sort the `month` field in custom order
// h_month_number_of["March"] = 3 
// h_month_number_of["August"] = 8
// h_month_number_of["December"] = 12
// #####################################################################
let h_month_number_of = {
    January: 1,
    February: 2,
    March: 3,
    April: 4,
    May: 5,
    June: 6,
    July: 7,
    August: 8,
    September: 9,
    October: 10,
    November: 11,
    December: 12,
};


// M21. define groups:
// groupBy_CASE:To group each `page` of the `pages` by `page.month` 
//              (Results:)(key=G1.key)(values=G1.rows)
//
//              Each `G1.key` is a value from the `page.month`.
//              Each `G1.rows` is an Array of JavaScript Objects.
// 
// sort_CASE:To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order
// 
// groupIn_CASE:To group each `G1.rows` of the `G1` by `G1.rows.week`
//              (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
// 
//              Each `G2.key` is a value from the `G1.rows.week`.
//              Each `G2.rows` is an Array of JavaScript Objects.
//
//              The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`
// #####################################################################
let groups = pages
    .groupBy((page) => page.month) // groupBy: (default) in ascending order
    .sort((G1) => h_month_number_of[G1.key], "asc")
    .groupIn((G1) => G1.week); // groupIn: (default) in ascending order

```

Q2: How to sort each `G1.rows` by `G2.key`(`week`)(H6) in descending order? (DVJS10:M31.FR12)

Summary_Q2

A2_21:

Original Example:

```dataviewjs
// M31. output groups:
// #####################################################################
for (let G1 of groups) {
    
    // M31.FR10 output G1.key: H3
    // G1.key: the value of the `month` field
    // Q29_GroupIn_CASE: "March", "August", "December"
    // #####################################################################
    dv.header(3, G1.key);


    // M31.FR12 sort G1.rows: in descending order
    // G1.rows.key: the value of the `week` field
    // Q29_GroupIn_CASE: "1", "2", "3", "4", "5"
    // G2.key=G1.rows.key
    // #####################################################################
    G1.rows = G1.rows.sort((G2) => G2.key, "desc");  
    
}

```

Q3: How to sort each `G2.rows` by `G2.rows.Cost`(`page.Cost`)? (DVJS10:M11, M31.FR20.FR70)

Summary_Q3

Original Example: Q3 (To be modified) (Method01)

```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q29_GroupIn/Q29_test_data"'
    )
    .where((page) => page.month)
    .where((page) => page.week)
    // .sort((page) => page.Cost, "asc");


        // M31.FR20.FR70 sort Cost: LIST
        // #####################################################################
        // Remarked by Justdoitcc 2023-01-14 20:00
        // Display the result as a list
        G2.rows = G2.rows.sort((page) => page.Cost, "asc");

```

A3_31:

Another Example: Method02

```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q29_GroupIn/Q29_test_data"'
    )
    .where((page) => page.month)
    .where((page) => page.week)
    .sort((page) => page.Cost, "asc");



        // M31.FR20.FR70 sort Cost: LIST
        // #####################################################################
        // Remarked by Justdoitcc 2023-01-14 20:00
        // Display the result as a list
        // G2.rows = G2.rows.sort((page) => page.Cost, "asc");

```

Code DVJS10_groupBy_month_groupIn_week_and_LIST

Summary_code

title: DVJS10_groupBy_month_groupIn_week_and_LIST =>1.To group each `page` of the `pages` by `page.month` (Results:)(key=G1.key)(values=G1.rows)  2.To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order  3.To group each `G1.rows` of the `G1` by `G1.rows.week` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`}  4.To display the result:  4.1 To display each `G1.key`(`month`) as a heading element(H3)("asc")  4.2 To display each `G2.key`(`week`) as a heading element(H6)("asc") 4.2.1 The H6 is "Week 1", "Week 2", "Week 3", "Week 4", or "Week 5" 4.3 To display each `G2.rows`("Cost", "Company", "Method", "Link") as a list (`Cost`:"asc")  
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q29_GroupIn/Q29_test_data"'
    )
    .where((page) => page.month)
    .where((page) => page.week)
    // .sort((page) => page.Cost, "asc");



// M13. define h_month_number_of:
// Features: To transform a month into a number
// Purposes: To sort the `month` field in custom order
// h_month_number_of["March"] = 3 
// h_month_number_of["August"] = 8
// h_month_number_of["December"] = 12
// #####################################################################
let h_month_number_of = {
    January: 1,
    February: 2,
    March: 3,
    April: 4,
    May: 5,
    June: 6,
    July: 7,
    August: 8,
    September: 9,
    October: 10,
    November: 11,
    December: 12,
};


// M15. define h_month_number_of:
// To transform a week string into a string like "Week 1" 
// which is to be a heading(H6)
// #####################################################################
let h_week_WeekSpaceNumber_of = {
    "1": "Week 1",
    "2": "Week 2",
    "3": "Week 3",
    "4": "Week 4",
    "5": "Week 5"
};


// M21. define groups:
// groupBy_CASE:To group each `page` of the `pages` by `page.month` 
//              (Results:)(key=G1.key)(values=G1.rows)
//
//              Each `G1.key` is a value from the `page.month`.
//              Each `G1.rows` is an Array of JavaScript Objects.
// 
// sort_CASE:To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order
// 
// groupIn_CASE:To group each `G1.rows` of the `G1` by `G1.rows.week`
//              (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
// 
//              Each `G2.key` is a value from the `G1.rows.week`.
//              Each `G2.rows` is an Array of JavaScript Objects.
//
//              The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`
// #####################################################################
let groups = pages
    .groupBy((page) => page.month) // groupBy: (default) in ascending order
    .sort((G1) => h_month_number_of[G1.key], "asc")
    .groupIn((G1) => G1.week); // groupIn: (default) in ascending order

    
// M30. output groups: formatted by Prettier - Code formatter v9.5.0 in VScode
// #####################################################################
// dv.span("The following is the content of the `groups`.\n");
// dv.span(JSON.stringify(groups, null, 2), "\n");


// M31. output groups:
// #####################################################################
for (let G1 of groups) {

    // M31.FR10 output G1.key: H3
    // G1.key: the value of the `month` field
    // Q29_GroupIn_CASE: "March", "August", "December"
    // #####################################################################
    dv.header(3, G1.key);


    // M31.FR12 sort G1.rows: in descending order
    // G1.rows.key: the value of the `week` field
    // Q29_GroupIn_CASE: "1", "2", "3", "4", "5"
    // G2.key=G1.rows.key
    // #####################################################################
    // G1.rows = G1.rows.sort((G2) => G2.key, "desc");
    

    // M31.FR20 output G1.rows:
    // #####################################################################
    for (let G2 of G1.rows) {
    
    
        // M31.FR20.FR11 output G2.key: H6
        // To transform the G2.key into the h_week_WeekSpaceNumber_of[G2.key]
        // Q29_GroupIn_CASE: "Week 1", "Week 2", "Week 3", "Week 4", "Week 5"
        // #####################################################################
        // dv.header(6, G2.key);
        dv.header(6, h_week_WeekSpaceNumber_of[G2.key]);


        // M31.FR20.FR51 output page: TABLE
        // #####################################################################
        // Display the result as a table
        // dv.table(
        //     ["Cost", "Company", "Method", "Link"],
        //     G2.rows
        //         .sort((page) => page.Cost, "asc")
        //         .map((page) => [
        //             page.Cost,
        //             page.Company,
        //             page.Method,
        //             page.file.link,
        //         ])
        // );
        

        // M31.FR20.FR70 sort Cost: LIST
        // #####################################################################
        // Remarked by Justdoitcc 2023-01-14 20:00
        // Display the result as a list
        G2.rows = G2.rows.sort((page) => page.Cost, "asc");


        // M31.FR20.FR71 output G2.rows: LIST
        // #####################################################################
        // Remarked by Justdoitcc 2023-01-14 20:00
        // Display the result as a list
        for (let page of G2.rows) {


            // M31.FR20.FR71.FR10 define a_List:
            // #####################################################################
            // Remarked by Justdoitcc 2023-01-14 20:00
            let a_List = [
                "Cost=" + page.Cost,
                "Company=" + page.Company,
                "Method=" + page.Method,
                "Link=" + page.file.link,
            ];
            
            
            // M31.FR20.FR71.FR12 define s_List:
            // #####################################################################
            // Remarked by Justdoitcc 2023-01-14 20:00
            // let s_List = "• " + a_List.join("; ") + "; <br>";          
            let s_List = "- " + a_List.join("; ") + "; <br>";
            
            
            // M31.FR20.FR71.FR20 output page:
            // #####################################################################
            // Remarked by Justdoitcc 2023-01-14 20:00
            // Display the result as a list
            dv.span(s_List);


        // Remarked by Justdoitcc 2023-01-14 20:00
        // Display the result as a list
        }
    }
}


```

Screenshots(DVJS10)

Part 1/2 :

Part 2/2 :

DVJS20_groupBy_month_groupIn_week_and_TABLE

Summary

Main DVJS

Code Name	Data type	Group By	Purposes	Remark
DVJS20 _groupBy_month _groupIn_week _and_TABLE	`month`: a string like “January”, “February” , …, or “December” `week`: a number like 1, 2, 3, 4 or 5	yes (twice)	1.To group each `page` of the `pages` by `page.month` (Results:)(key=G1.key)(values=G1.rows) 2.To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order 3.To group each `G1.rows` of the `G1` by `G1.rows.week` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`} 4.To display the result: 4.1 To display each `G1.key`(`month`) as a heading element(H3)(“asc”) 4.2 To display each `G2.key`(`week`) as a heading element(H6)(“asc”) 4.2.1 The H6 is “Week 1”, “Week 2”, “Week 3”, “Week 4”, or “Week 5” 4.3 To display each `G2.rows`(“Cost”, “Company”, “Method”, “Link”) as a table (`Cost`:“asc”)	1.The DVJS0 is based on the DVJS10. 1.1 The original M31.FR20.FR70 and M31.FR20.FR71 are removed. 1.2 The original M31.FR20.FR51 comments are become codes.

Code DVJS20_groupBy_month_groupIn_week_and_TABLE

Summary_code

title: DVJS20_groupBy_month_groupIn_week_and_TABLE =>1.To group each `page` of the `pages` by `page.month` (Results:)(key=G1.key)(values=G1.rows) 2.To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order 3.To group each `G1.rows` of the `G1` by `G1.rows.week` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`} 4.To display the result: 4.1 To display each `G1.key`(`month`) as a heading element(H3)("asc") 4.2 To display each `G2.key`(`week`) as a heading element(H6)("asc") 4.2.1 The H6 is "Week 1", "Week 2", "Week 3", "Week 4", or "Week 5" 4.3 To display each `G2.rows`("Cost", "Company", "Method", "Link") as a table (`Cost`:"asc") 
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q29_GroupIn/Q29_test_data"'
    )
    .where((page) => page.month)
    .where((page) => page.week);



// M13. define h_month_number_of:
// Features: To transform a month into a number
// Purposes: To sort the `month` field in custom order
// h_month_number_of["March"] = 3 
// h_month_number_of["August"] = 8
// h_month_number_of["December"] = 12
// #####################################################################
let h_month_number_of = {
    January: 1,
    February: 2,
    March: 3,
    April: 4,
    May: 5,
    June: 6,
    July: 7,
    August: 8,
    September: 9,
    October: 10,
    November: 11,
    December: 12,
};


// M15. define h_month_number_of:
// To transform a week string into a string like "Week 1" 
// which is to be a heading(H6)
// #####################################################################
let h_week_WeekSpaceNumber_of = {
    "1": "Week 1",
    "2": "Week 2",
    "3": "Week 3",
    "4": "Week 4",
    "5": "Week 5"
};


// M21. define groups:
// groupBy_CASE:To group each `page` of the `pages` by `page.month` 
//              (Results:)(key=G1.key)(values=G1.rows)
//
//              Each `G1.key` is a value from the `page.month`.
//              Each `G1.rows` is an Array of JavaScript Objects.
// 
// sort_CASE:To sort each `G1.rows` of the `G1` by `h_month_number_of[G1.key]` in ascending order
// 
// groupIn_CASE:To group each `G1.rows` of the `G1` by `G1.rows.week`
//              (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
// 
//              Each `G2.key` is a value from the `G1.rows.week`.
//              Each `G2.rows` is an Array of JavaScript Objects.
//
//              The Bottom Level(Grouped Twice): `G2`=`G1.rows`; `G2.rows`=`page`
// #####################################################################
let groups = pages
    .groupBy((page) => page.month) // groupBy: (default) in ascending order
    .sort((G1) => h_month_number_of[G1.key], "asc")
    .groupIn((G1) => G1.week); // groupIn: (default) in ascending order

    
// M30. output groups: formatted by Prettier - Code formatter v9.5.0 in VScode
// #####################################################################
// dv.span("The following is the content of the `groups`.\n");
// dv.span(JSON.stringify(groups, null, 2), "\n");


// M31. output groups:
// #####################################################################
for (let G1 of groups) {

    // M31.FR10 output G1.key: H3
    // G1.key: the value of the `month` field
    // Q29_GroupIn_CASE: "March", "August", "December"
    // #####################################################################
    dv.header(3, G1.key);


    // M31.FR12 sort G1.rows: in descending order
    // G1.rows.key: the value of the `week` field
    // Q29_GroupIn_CASE: "1", "2", "3", "4", "5"
    // G2.key=G1.rows.key
    // #####################################################################
    // G1.rows = G1.rows.sort((gp) => gp.key, "desc");


    // M31.FR20 output G1.rows:
    // #####################################################################
    for (let G2 of G1.rows) {
    
    
        // M31.FR20.FR11 output G2.key: H6
        // To transform the G2.key into the h_week_WeekSpaceNumber_of[G2.key]
        // Q29_GroupIn_CASE: "Week 1", "Week 2", "Week 3", "Week 4", "Week 5"
        // #####################################################################
        // dv.header(6, G2.key);
        dv.header(6, h_week_WeekSpaceNumber_of[G2.key]);
        

        // M31.FR20.FR51 output page: TABLE
        // #####################################################################
        // Display the result as a table
        dv.table(
            ["Cost", "Company", "Method", "Link"],
            G2.rows
                .sort((page) => page.Cost, "asc")
                .map((page) => [
                    page.Cost,
                    page.Company,
                    page.Method,
                    page.file.link,
                ])
        );
        
    }
}


```

How do you group by twice in either dataview or dataviewjs?

Topic

Test

Input

dictionary files:

folder: R03

folder: A08

folder: D12

DVJS10_groupBy_month_groupIn_week_and_LIST

Main DVJS

Notes:

Q1: How to sort the `groups` by `G1.key`(`month`)(H3) in custom order? (DVJS10:M13+M21)

A1_11:

Original Example: A1_11

Q2: How to sort each `G1.rows` by `G2.key`(`week`)(H6) in descending order? (DVJS10:M31.FR12)

A2_21:

Original Example:

Q3: How to sort each `G2.rows` by `G2.rows.Cost`(`page.Cost`)? (DVJS10:M11, M31.FR20.FR70)

Original Example: Q3 (To be modified) (Method01)

A3_31:

Another Example: Method02

Code DVJS10_groupBy_month_groupIn_week_and_LIST

Screenshots(DVJS10)

Part 1/2 :

Part 2/2 :

DVJS20_groupBy_month_groupIn_week_and_TABLE

Main DVJS

Code DVJS20_groupBy_month_groupIn_week_and_TABLE

Screenshots(DVJS20)

Part 1/2 :

Part 2/2 :

Reference

Emojis

The groupIn function

How do you group by twice in either dataview or dataviewjs?

Topic

Test

Input

dictionary files:

folder: R03

folder: A08

folder: D12

DVJS10_groupBy_month_groupIn_week_and_LIST

Main DVJS

Notes:

Q1: How to sort the groups by G1.key(month)(H3) in custom order? (DVJS10:M13+M21)

A1_11:

Original Example: A1_11

Q2: How to sort each G1.rows by G2.key(week)(H6) in descending order? (DVJS10:M31.FR12)

A2_21:

Original Example:

Q3: How to sort each G2.rows by G2.rows.Cost(page.Cost)? (DVJS10:M11, M31.FR20.FR70)

Original Example: Q3 (To be modified) (Method01)

A3_31:

Another Example: Method02

Code DVJS10_groupBy_month_groupIn_week_and_LIST

Screenshots(DVJS10)

Part 1/2 :

Part 2/2 :

DVJS20_groupBy_month_groupIn_week_and_TABLE

Main DVJS

Code DVJS20_groupBy_month_groupIn_week_and_TABLE

Screenshots(DVJS20)

Part 1/2 :

Part 2/2 :

Reference

Emojis

The groupIn function

Q1: How to sort the `groups` by `G1.key`(`month`)(H3) in custom order? (DVJS10:M13+M21)

Q2: How to sort each `G1.rows` by `G2.key`(`week`)(H6) in descending order? (DVJS10:M31.FR12)

Q3: How to sort each `G2.rows` by `G2.rows.Cost`(`page.Cost`)? (DVJS10:M11, M31.FR20.FR70)