How to handle tags and tag groups in pivot table for analysis?

Webix JS
#1

Hi,

I’m trying to use a data structure that has a lot of tag-based columns. How can I get webix to recognize this properly?

Here is example data:

[
    {
        "_id": "1",
        "symbol": "sym1",
        "open_date": "2019-04-23",
        "_type": "t1",
        "currency": "USD",
        "quantity": 456,
        "tags": [
            {
                "tag": "G_TAG1",
                "group_name": "Group_1",
            },
            {
                "tag": "G_TAG2",
                "group_name": "Group_1",
            }
        ],
	},
    {
        "_id": "2",
        "symbol": "sym2",
        "open_date": "2029-01-11",
        "_type": "t2",
        "currency": "EUR",
        "quantity": 312,
        "tags": [
            {
                "tag": "G3_TAG-ABC",
                "group_name": "Group_3",
            },
            {
                "tag": "G2_TAG-DEF",
                "group_name": "Group_2",
            },
            {
                "tag": "G_TAG3",
                "group_name": "Group_1",
            }
        ],
	},
    {
        "_id": "3",
        "symbol": "sym3",
        "open_date": "2010-12-01",
        "_type": "t3",
        "currency": "AUD",
        "quantity": 123,
        "tags": [
            {
                "tag": "G_TAG3",
                "group_name": "Group_1",
            }
        ],
	}
]

As you can see above, each data point has a list of tags and each tag can belong to a group or not. So the top-most hierarchy is the tag group and below that are the individual tags.

Is there a way webix can recognize this or a way I can transform the data so visualize per-tag or per-group statistics?

#2

Hello @teemomr

If I understand the use-case correctly, unfortunately, so far there is no simple solution for this case.

Pivot can only use key:value pairs (from the 1st level of a data item) as criteries/fields for data grouping and filtering.
Nested fields won’t be processed as separate values, especially if there are several maching keys with different values.

Depending on the desired structure, the data can be transformed in a set that Pivot can process, i.e. create unique values for all tags and all groups.
However, the transformed dataset will be usable only in specific conditions where the tags and groups are needed - for a structure without these parameters, there’ll be duplicated items (check the log in the snippet below).
Here’s the example of data transformed separately for groups and for groups+tags (this demo is only to display the data): Code Snippet

If this example of grouping matches the use-case, the most feasible solution is to use external data processing (client-side Pivot will request and display data, while the actual calculations will be performed on the server): Code Snippet.

Our implementation of external processing uses the same math engine as Pivot does on the client-side, so it’ll still require additional development for the server-side in order to adjust the data and divide the fields similar to the first snippet.

#3

Hi,

Thank you for your detailed response. This is still not quite what I expected.
I am a dev and I’m fine with doing backend or frontend processing, but the result has to be exactly as I mentioned in my original post: pivot view with the ability to split tag groups.

The 3rd table in your first close snippet is the closest to what I wanted to see.

I’ve also been testing your competitor aggrid and this is what they came up with: Plunker - Simple Example

Is this exact functionality achievable? try clicking on the pivot and change back to the table - you will notice that the tags and groups are automatically grouped correctly in table vs pivot mode.

#4

I just took another look at the code and it seems you have the same approach as the snippet in ag-grid.

One thing I noticed is:

Once I select all the fields as rows and nothing else, the table reverts to a normal HTML table and it also affects the other pivot grid. Can this be avoided? I want the table to still be filterable if it’s a normal flat table

Edit: I have no idea what’s happening here. where is the pivot settings on the right side? did it disappear to outside the viewport? I encountered this in all examples…
https://snippet.webix.com/hw7prtjt/material

recording:
https://file.io/4oK7oj9bj66e

#5

Thank you for the clarification!
At first, I thought that the data structure might vary depending on whether or not the tags are used in grouping (since initially there are three items with unique _id)

If the raw data should have a constant structure, the modifications could be easily made on the client-side right after the data was loaded. Code Snippet

Is this exact functionality achievable? try clicking on the pivot and change back to the table - you will notice that the tags and groups are automatically grouped correctly in table vs pivot mode.

Webix Pivot is similar to the “pivot mode” only. The “Table” mode of our Pivot is intended for the data aggregated from the incoming dataset, which in this case is the extractedData from the snippet above.
The mentioned part of functionality can be achieved by adding another widget (Datatable) to the app with one of the two options:

  • (the easiest one) make two separate views (Pivot and Datatable) and switch between them
    Code Snippet
  • (more complex, but all will be wrapped in one Pivot widget) embed a Datatable as a new view to Pivot, store the original dataset for it and add specific data processing (build headers/UI to control the visibility of columns)

The visibility of columns in the Datatable can be controlled either with header menu (enabled in the above snippet) or with a custom list with checkboxes.

Once I select all the fields as rows and nothing else, the table reverts to a normal HTML table and it also affects the other pivot grid. Can this be avoided? I want the table to still be filterable if it’s a normal flat table

The rows serve as criteria for grouping and also will be pinned (frozen).
By default, frozen columns must be displayed in full, which can increase the width of the table, which in turn forces Pivot to overflow its container (body) and shift the position of the configuration window.
As a solution, you can disable this kind of behavior using freezeColumns: false.

Please, check the following example: Code Snippet

#6

Thanks for the detailed info. I will look into this.