Merge cells in dataview for grouped content

Help
2

Topic : 1/2

Summary

INFO:
Continuing the discussions from Q16_GroupIn, mentioned in the Reference:

  • How to group by a column like Residencia and group by a column like Clan on a already grouped data? (DVJS01)
  • How to group by a column like Residencia, group by a column like Clan on a already grouped data and Merge three cells, such as “Cainita”, “Generacion”, and “Status”? (DVJS03, DVJS10)

NOTE:

  1. The only reason to merge three cells is that not every field of them is defined.
  2. Take the file dic_19580301 as an example. Both the field Generacion and Status strictly equal null.
  3. Take the file dic_19580801 as an example. The field Generacion strictly equals undefined.

Test

Summary
  • dataview: v0.5.55

Input

Summary

dictionary files

NOTE: The dictionary files are based on the files in the Q16_GroupIn, mentioned in the Reference.

  • Location: “100_Project/01_dataviewjs/01_by_example/Q32_GroupIn_MergeCells/Q32_test_data”

folder: 01

  • filename : dic_19580101
```yaml
---
Date: 1958-01-01
---
Residencia:: BUENOS AIRES
Clan:: Malkavian
Cainita:: Raguela
Generacion:: 9
Status:: Prominente
```

folder: 02

  • filename : dic_19580201
```yaml
---
Date: 1958-02-01
---
Residencia:: BUENOS AIRES
Clan::  Tremere
Cainita:: Liandra Vespa
Generacion:: 10
Status:: Regente
```

folder: 03_Generacion_Status_null

  • filename : dic_19580301
```yaml
---
Date: 1958-03-01
---
Residencia:: BUENOS AIRES
Clan:: Ventrue
Cainita:: Vortex

%%6%%
Generacion:: 

%%En Desgracia%%
Status:: 

```

folder: 04_Generacion_null

  • filename : dic_19580401
```yaml
---
Date: 1958-04-01
---
Residencia:: BUENOS AIRES
Clan:: Ventrue
Cainita:: Maurtice Durant

%%10%%
Generacion:: 
Status:: Neonato
```

folder: 05

  • filename : dic_19580501
```yaml
---
Date: 1958-05-01
---
Residencia:: PARIS
Clan:: Toreador
Cainita:: Luis Bryon
Generacion:: 5
Status:: Principe
```

folder: 06

  • filename : dic_19580601
```yaml
---
Date: 1958-06-01
---
Residencia:: PARIS
Clan:: Toreador
Cainita:: Chevalier d'Eglantine
Generacion:: 6
Status:: Lider Sheriff
```

folder: 07

  • filename : dic_19580701
```yaml
---
Date: 1958-07-01
---
Residencia:: USA
Clan:: Toreador
Cainita:: Helena
Generacion:: 4
Status:: Incognita
```

folder: 08_Generacion_undefined

  • filename : dic_19580801
```yaml
---
Date: 1958-08-01
---
Residencia:: PARIS
Clan:: Toreador
Cainita:: Julia Grabher

%%Generacion=8%%

Status:: Nconato
```

folder: 10_excluded

  • filename : dic_19581001
```yaml
---
Date: 1958-10-01
---

```

DVJS01_groupBy_Residencia_groupIn_Clan_and_TABLE

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS01
_groupBy_Residencia
_groupIn_Clan_and_TABLE		 Residencia:
a string
1.“BUENOS AIRES”
2.“PARIS”
3.“USA”

Clan:
a string
1.“Malkavian”
2.“Toreador”
3.“Tremere”
4.“Ventrue”		 yes
(twice)		 1.To group each page of the pages by page.Residencia
(Results:)(key=G1.key)(values=G1.rows)

2.To group each G1.rows of the G1 by G1.Clan
(Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
{The Bottom Level(Grouped Twice): G2=G1.rows; G2.rows=page}

3.To display the result:
3.1 To display each G1.key(Residencia) as a heading element(H4)(“asc”)
3.2 To display each G2.rows(“Clan”, “Cainita”, “Generacion”, “Status”) as a table(Clan:“asc”)		 The DVJS01 is the same as the DVJS20 in the Q16_GroupIn, mentioned in the Reference.

Code DVJS01_groupBy_Residencia_groupIn_Clan_and_TABLE

Summary_code 
title: DVJS01_groupBy_Residencia_groupIn_Clan_and_TABLE => 1.To group each `page` of the `pages` by `page.Residencia` (Results:)(key=G1.key)(values=G1.rows) 2.To group each `G1.rows` of the `G1` by `G1.Clan` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`;` G2.rows`=`page`} 3.To display the result: 3.1 To display each `G1.key`(`Residencia`) as a heading element(H4)("asc") 3.2 To display each `G2.rows`("Clan", "Cainita", "Generacion", "Status") as a table(`Clan`:"asc")
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages('"100_Project/01_dataviewjs/01_by_example/Q32_GroupIn_MergeCells/Q32_test_data"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.Residencia)
    .where((page) => page.Clan)
    .sort((page) => page.Generacion, "asc");     



// M21. define groups:
// groupBy_CASE:To group each `page` of the `pages` by `page.Residencia` 
//              (Results:)(key=G1.key)(values=G1.rows)
//
//              Each `G1.key` is a value from the `page.Residencia`.
//              Each `G1.rows` is an Array of JavaScript Objects.
// 
// (comments)sort_CASE:To sort each `G1.rows` of the `G1` by `G1.key` in descending order
// 
// groupIn_CASE:To group each `G1.rows` of the `G1` by `G1.rows.Clan`
//              (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
// 
//              Each `G2.key` is a value from the `G1.rows.Clan`.
//              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.Residencia) // groupBy: (default) in ascending order
    // .sort((G1) => G1.key, "desc")
    .groupIn((G1) => G1.Clan); // groupIn: (default) in ascending order


// M81. output groups: groupBy_Residencia and groupIn_Clan
// #####################################################################
dv.header(2, "M81. output groups: groupBy_Residencia and groupIn_Clan");
for (let G1 of groups) {
    dv.header(4, G1.key);
    dv.table(
        ["Clan", "Cainita", "Generacion", "Status"],
        G1.rows
            .sort((G2) => G2.rows.Clan, "asc")
            .map((G2) => [
                G2.rows.Clan[0],
                G2.rows.Cainita,
                G2.rows.Generacion,
                G2.rows.Status,
              
            ])
    );
}



```

Screenshots(DVJS01):

20230305_Q32_DVJS01_output_a01
20230305_Q32_DVJS01_output_a011470×1050 96.2 KB

DVJS03_groupBy_Residencia_groupIn_Clan_and_Merge_three_cells_and_TABLE

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS03
_groupBy_Residencia
_groupIn_Clan
_and_Merge_three_cells
_and_TABLE		 Residencia:
a string
1.“BUENOS AIRES”
2.“PARIS”
3.“USA”

Clan:
a string
1.“Malkavian”
2.“Toreador”
3.“Tremere”
4.“Ventrue”

(A customizable variable:
Modify it as needs.)
a_DVIF:
[“Cainita”, “Generacion”, “Status”]

 yes
(twice)		 1.To group each page of the pages by page.Residencia (Results:)(key=G1.key)(values=G1.rows)

2.To group each G1.rows of the G1 by G1.Clan (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): G2=G1.rows; G2.rows=page}

3.To merge three cells:
3.1 in each page, define a new field P_Cainita_Generacion_Status, which is the final merged Column Name for such column P_Cainita_Generacion_Status
3.2 define s_ColumnHeader_of_P_Cainita_Generacion_Status, which is the final merged Column Header for such column P_Cainita_Generacion_Status

4.To display the result: (Clan:“asc”)
4.1 To display each G1.key(Residencia) as a heading element(H4)(“asc”)
4.2 To display each G2.rows([Clan[0], P_Cainita_Generacion_Status]) as a table([“Clan”, s_ColumnHeader_of_P_Cainita_Generacion_Status])		 1.The DVJS03 is based on the DVJS01 in the topic.
1.1 The Purpose 3 is added.
1.2 The Purpose 4.2 is modified.

Notes:

Summary

Q1: What does the following code mean? (DVJS03:M15.FR10)

Summary_Q1
Original Example: Q1 (To be explained)
```dataviewjs
    // M15.FR10 update page[e] in each page: if it strictly equals null or undefined
    // CASE01: update a field `Cainita` in each page
    // CASE02: update a field `Generacion` in each page
    // CASE03: update a field `Status` in each page
    // #####################################################################
    ["Cainita", "Generacion", "Status"].forEach((e) => {
        if (page[e] === null || page[e] === undefined) {
            page[e] = "";
        }
    });
```

A1_11: M15.FR10 ＝ M15.FR11 + M15.FR13 + M15.FR15

Another Example: A1_11
```dataviewjs
    // M15.FR11 update `Cainita` in each page: if it strictly equals null or undefined
    // update pages: update a field `Cainita` in each page
    // #####################################################################
    if (page.Cainita === null || page.Cainita === undefined) {
        page.Cainita = "";
    }
    
    
    // M15.FR13 update `Generacion` in each page: if it strictly equals null or undefined
    // update pages: update a field `Generacion` in each page
    // #####################################################################
    if (page.Generacion === null || page.Generacion === undefined) {
        page.Generacion = "";
    }
    
    
    // M15.FR15 update `Status` in each page: if it strictly equals null or undefined
    // update pages: update a field `Status` in each page
    // #####################################################################
    if (page.Status === null || page.Status === undefined) {
        page.Status = "";
    }

```

Code DVJS03_groupBy_Residencia_groupIn_Clan_and_Merge_three_cells_and_TABLE

Summary_code 
title: DVJS03_groupBy_Residencia_groupIn_Clan_and_Merge_three_cells_and_TABLE=> 1.To group each `page` of the `pages` by `page.Residencia` (Results:)(key=G1.key)(values=G1.rows) 2.To group each `G1.rows` of the `G1` by `G1.Clan` (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows) {The Bottom Level(Grouped Twice): `G2`=`G1.rows`;` G2.rows`=`page`} 3.To merge three cells:  3.1 in each page, define a new field `P_Cainita_Generacion_Status`, which is the final merged Column Name for such column `P_Cainita_Generacion_Status` 3.2 define `s_ColumnHeader_of_P_Cainita_Generacion_Status`, which is the final merged Column Header for such column `P_Cainita_Generacion_Status` 4.To display the result: (`Clan`:"asc") 4.1 To display each `G1.key`(`Residencia`) as a heading element(H4)("asc")  4.2 To display each `G2.rows`([`Clan[0]`, `P_Cainita_Generacion_Status`]) as a table(["Clan", `s_ColumnHeader_of_P_Cainita_Generacion_Status`])
collapse: close
icon: 
color: 
```dataviewjs
// M06. define a_DVIF: A customizable variable
// definition: The original column names to be merged
//             are "Cainita", "Generacion", and "Status".
// The prefix "a" of the variable a_DVIF means an array.
// Tips: Modify it as needs.
// #####################################################################
let a_DVIF = ["Cainita", "Generacion", "Status"];


// M07. define I_SPECIFIED_COLUMN_WIDTH: A customizable variable
// definition: The column width for each original column to be merged
//             "Cainita", "Generacion", and "Status"
// The prefix "I" of the variable I_SPECIFIED_COLUMN_WIDTH means an integer.
// Tips: Modify it as needs.
// #####################################################################
const I_SPECIFIED_COLUMN_WIDTH = 32;// 25 or 32 or 38


// M08. define I_MIN_COUNT_TO_PADRIGHT: A customizable variable
// definition: The minimum value to be padded to the right for each original column
// Tips: Modify it as needs.
// #####################################################################
const I_MIN_COUNT_TO_PADRIGHT = 5;


// M09. define I_VALUE_TO_OFFSET_FOR_COLUMN_HEADER: A customizable variable
// definition: The value for the final merged Column Header 
//             `s_ColumnHeader_of_P_Cainita_Generacion_Status` to be offsetted 
//             according to the `I_SPECIFIED_COLUMN_WIDTH`
// Tips: Modify it as needs.
// #####################################################################
const I_VALUE_TO_OFFSET_FOR_COLUMN_HEADER = 6;


// M11. define pages: gather all relevant pages
// #####################################################################
let pages = dv
    .pages('"100_Project/01_dataviewjs/01_by_example/Q32_GroupIn_MergeCells/Q32_test_data"')
    .where((page) => dv.func.contains(page.file.name, "dic_"))
    .where((page) => page.Residencia)
    .where((page) => page.Clan)
    .sort((page) => page.Generacion, "asc");     


// M15. define P_Cainita_Generacion_Status in each page:
// update pages: add a new field `P_Cainita_Generacion_Status` into each page
//
// "Raguela                         9                               Prominente                      "
//  |<-     32 characters          -><-     32 characters          -><-     32 characters          ->|
//
// #####################################################################
pages.forEach((page) => {
    
    // M15.FR10 update page[e] in each page: if it strictly equals null or undefined
    // EMPTY_CASE_11: update a field `Cainita` in each page
    // EMPTY_CASE_12: update a field `Generacion` in each page
    // EMPTY_CASE_13: update a field `Status` in each page
    // #####################################################################
    a_DVIF.forEach((e) => {
        if (page[e] === null || page[e] === undefined) {
            page[e] = "";
        }
    });


    // M15.FR20 define P_Cainita_Generacion_Status in each page
    // update pages: add a new field `P_Cainita_Generacion_Status` into each page
    // `&nbsp;`: It stands for non-breaking space, which is the space generated 
    //           by the space key on the general keyboard.
    // #####################################################################
    page.P_Cainita_Generacion_Status = a_DVIF
        .map((e) => {
            let count = I_SPECIFIED_COLUMN_WIDTH - page[e].toString().length;
            if (count < 0) {
                count = I_MIN_COUNT_TO_PADRIGHT;
            }
            // return dv.func.padright(page[e] + "", count, " ");
            return page[e] + "&nbsp;".repeat(count);
        })
        .join("");
        

    // M15.FR22 update P_Cainita_Generacion_Status in each page
    // <pre>: The <pre> tag defines preformatted text.
    // Text in a <pre> element is displayed in a fixed-width font, and 
    // the text preserves both spaces and line breaks.
    // #####################################################################
    page.P_Cainita_Generacion_Status =
        "<pre>" + page.P_Cainita_Generacion_Status + "</pre>";
});


// M17. define s_ColumnHeader_of_P_Cainita_Generacion_Status:
// the Column Header of the field P_Cainita_Generacion_Status in each page
//
// "Cainita                   Generacion                Status                    "
// |<-     32-6 characters  -><-     32-6 characters  -><-     32-6 characters  ->|
//
// #####################################################################
let s_ColumnHeader_of_P_Cainita_Generacion_Status = a_DVIF
    .map((e) => {
        let count =
            I_SPECIFIED_COLUMN_WIDTH -
            I_VALUE_TO_OFFSET_FOR_COLUMN_HEADER -
            e.toString().length;
        if (count < 0) {
            count = I_MIN_COUNT_TO_PADRIGHT;
        }
        return e + "&nbsp;".repeat(count);
    })
    .join("");


// M19. update s_ColumnHeader_of_P_Cainita_Generacion_Status:
// #####################################################################
s_ColumnHeader_of_P_Cainita_Generacion_Status =
    "<pre>" + s_ColumnHeader_of_P_Cainita_Generacion_Status + "</pre>";


// M21. define groups:
// groupBy_CASE:To group each `page` of the `pages` by `page.Residencia` 
//              (Results:)(key=G1.key)(values=G1.rows)
//
//              Each `G1.key` is a value from the `page.Residencia`.
//              Each `G1.rows` is an Array of JavaScript Objects.
// 
// (comments)sort_CASE:To sort each `G1.rows` of the `G1` by `G1.key` in descending order
// 
// groupIn_CASE:To group each `G1.rows` of the `G1` by `G1.rows.Clan`
//              (Results:)(key=G2.key)(values=G2.rows=G1.rows.rows)
// 
//              Each `G2.key` is a value from the `G1.rows.Clan`.
//              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.Residencia) // groupBy: (default) in ascending order
    // .sort((G1) => G1.key, "desc")
    .groupIn((G1) => G1.Clan); // groupIn: (default) in ascending order


// M81. output groups: groupBy_Residencia and groupIn_Clan
// #####################################################################
dv.header(2, "M81. output groups: groupBy_Residencia and groupIn_Clan with Merging cells");
for (let G1 of groups) {
    dv.header(4, G1.key);
    dv.table(
        ["Clan", s_ColumnHeader_of_P_Cainita_Generacion_Status],
        G1.rows
            .sort((G2) => G2.rows.Clan, "asc")
            .map((G2) => [
                G2.rows.Clan[0],          
                G2.rows.P_Cainita_Generacion_Status,          
               
            ])
    );
}



```

Screenshots(DVJS03):

Part 1/2:

20230306_Q32_DVJS03_output_a01
20230306_Q32_DVJS03_output_a011470×1050 103 KB

Part 2/2:

20230306_Q32_DVJS03_output_a02
20230306_Q32_DVJS03_output_a021470×770 22.3 KB

Conclusion

Summary
  • Use the DVJS10 instead of the DVJS03.

Reference

Summary

Q16_GroupIn: DVJS20

Q28_eHeadings > part01 > DVJS10 > M31.FR50.FR31B

Summary 
```dataviewjs
        // M31.FR50.FR31B s_single_line_with_bullet_point:
        // BULLET_CASE_12: To display the original level of each heading
        // bullet point = Lower Three Quarters Block "▆" U+2586
        // #####################################################################
        if (b_display_original_level === true) {
            s_single_line_with_bullet_point =
                "▆".repeat(i_Level_Of_Heading) +
                " " +
                s_Heading_Without_Hashtag;
        }
```