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

Adamson · January 8, 2023, 3:31am

Things I have tried

This doesn’t work:

TABLE rows.field_a, rows.field_b, rows.field_c
GROUP BY month
GROUP BY week

I can get the dataviewjs to group once by month but not again by week…

let pages = dv.pages()

let groups = pages.groupBy(p => p.month);



for (let group of groups){
	dv.header(2, group.key);
	dv.table(["rows.field_a", "rows.field_b", "rows.field_c"],
		group.rows
			
			.map(b => [b.field_a, b.field_b, b.field_c])
			
	)
}

What I’m trying to do

Imagine there are a bunch of pages in an obsidian vault with a month:: field and a week:: field.

I want generate a table (or list) in either dataview or dataviewjs that groups notes by month and then by week so that the result (generally speaking) looks like this:

January

Week 1

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3

Week 2

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3

Week 3

table row (or list item) 1
table row (or list item) 2

Week 3

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3
table row (or list item) 4

Week 4

table row (or list item) 1

February

Week 1

table row (or list item) 1

Week 2

table row (or list item) 1

Week 3

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3

Week 3

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3
table row (or list item) 4

Week 4

table row (or list item) 1

March

Week 1

table row (or list item) 1

Week 2

table row (or list item) 1

Week 3

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3

Week 3

table row (or list item) 1
table row (or list item) 2
table row (or list item) 3
table row (or list item) 4

Week 4

table row (or list item) 1

April

(pattern continues as you would expect… Month headers are followed by week headers, inside of which are nested table rows or list items…)

justdoitcc · January 8, 2023, 6:51am

You could look at the following documents.
Q16_GroupIn: Solutions
Q17_GroupIn: The DVJS20 from Solutions

Adamson · January 9, 2023, 10:02am

I already had a look at those solutions, but they didn’t seem to capture what I am after. They are very thorough solutions, however.

holroy · January 9, 2023, 10:15pm

Using your query as a starter, I see that within each group (or month) object, you can actually do the same GROUP BY operation, like you did on pages. But you need to do it on the group.rows variable. So you were very close to actually solving it yourself.

Full script

```dataviewjs
let pages = dv.pages()

console.log('Pages', pages)
let months = pages.groupBy(p => p.month);

for (let month of months){
  console.log('Month', month)
  dv.header(2, `Month ${ month.key }`);
  const weeks = month.rows.groupBy(g => g.week);
    
  for (let week of weeks) {
    console.log('Week', week)
    dv.header(3, `Week ${ week.key }`)
      
    dv.table(["file", "rows.month", "rows.week"],
          week.rows.map(m => [m.file?.name, m.month, m.week]))
  }	
}
```

You don’t need the console.log() statements, but if you expand those in the Developer Tools > Console, you’ll see that pages is similar to month.rows which also is similar to week.rows. All being Proxy’s having a Target of DataArrayImpl, which allows for GROUP BY and all the other fun functions.

With my test setup, this script generates this output:

My <h2>'s are orange, and my <h3>'s are yellow, thanks to colorful headers of Minimal .

holroy · January 10, 2023, 4:06pm

I’m not sure whether I was in a bad or good mood last night, but I started fiddling about with whether this was doable or not, and I came as far as this:

```dataview
TABLE WITHOUT ID 
  rows.month,
  rows.rows.week, 
  rows.rows.file.name
FROM #f51737
GROUP BY month
GROUP BY rows.week
SORT rows.month
```

Which produces this output on my test data:

However, this produces the weekly results as lists within that table, but it does give the correct weeks and files available in my test set. Can we do better?!

I then came up with this monstrosity:

```dataview
TABLE WITHOUT ID 
  M, W, item.file.link
FROM #f51737
GROUP BY month
GROUP BY rows.rows.week
FLATTEN rows.month as M
FLATTEN rows.rows.week[0] as W
FLATTEN
  filter(
    rows.rows[0], 
    (F) => F.week = W AND F.month = M) as item
SORT item.file.link
```

Which gave this output:

But there is something wrong with that last filter operation, see how it doubles up in month 2, week 2, where my test set had two files for each week.

One can insert item or rows.rows[0] (or rows.rows) into the table, to see available data, but I have so far not been able to single out just the correct bits and pieces.

And I’m also wondering if one actually needs yet another FLATTEN to be able to separate out individual file for a given month and week.

If anyone feel (strange? / up for it / …) please go ahead and tamper even more with this concept. I think I’m done for now.

Summary

It’s kind of doable using only dataview to do this double GROUP BY, but it gets messy, very fast, and it’s hard to process (at least for me ). And even if one would get the “correct” data set out of it, aka month, week, "file object" you’re not anywhere near the correct formatting you originally wanted.

So in my book, go for the dataviewjs solution. It’s easy, and maintainable.

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,
                ])
        );
        
    }
}


```

Screenshots(DVJS20)

Part 1/2 :

Part 2/2 :

Reference

Summary

Emojis

Bullet: • black small circle U+2022

The groupIn function

Q16_GroupIn: The DVJS20_A2_21 from Solutions
Q17_GroupIn: The DVJS20 from Solutions

justdoitcc · January 15, 2023, 3:19am

Summary

Thanks for your reminder.
The Q16_GroupIn is updated.
- The Q2 and Q3 are added in the DVJS20 Q&A.
You no longer need to read it because the DVJS10_groupBy_month_groupIn_week_and_LIST and DVJS20_groupBy_month_groupIn_week_and_TABLE are based on it.
What you have to do is figure out what’s going on with M13 and M15.

holroy · January 15, 2023, 8:57am

Might I ask why you use this cryptic form to hide away your good answers? Do you have an automated system for generating your answers?

justdoitcc · January 15, 2023, 2:03pm

Summary

Thank you for highlighting the key points.
Most solutions I write are not very short. For example, the number of characters in the solutions for this topic is 15,247. Especially on a cell phone, it is not easy to scroll the mouse to skip (or read) such a long article. However, after the contents are hidden, it is easy for users to quickly select what they are interested in to read. I am also one of the users because I often refer to what I write on this website. It is also very helpful for myself to quickly read the DVJS10, DVJS20, or DVJS30.
Rome wasn’t built in a day. I started writing the solutions for the Q16_GroupIn on 2022-08-19. I started writing the solutions for the Q17_GroupIn on 2022-08-25. Both of them are related “grouping by twice”. Because the solutions for the Q16_GroupIn is very similar to this topic, I added two Q&A like Q2 and Q3(the DVJS20_A2_21 and DVJS20_A3_31) at 2023-01-15T10:00. Therefore, I just copied the DVJS20_A2_21 and did some string replacements. Furthermore, I added two steps like the M13 and M15. To sum up, there is no automated system for generating my solutions.

system · February 11, 2023, 8:50am

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

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

Things I have tried

What I’m trying to do

January

Week 1

Week 2

Week 3

Week 3

Week 4

February

Week 1

Week 2

Week 3

Week 3

Week 4

March

Week 1

Week 2

Week 3

Week 3

Week 4

April

Summary

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)