How to create Sub categories in Data table?

Shubhankar · January 30, 2024, 6:27pm

I have generated a data table using the Kibana 8 lens visualization, but I'm uncertain about adding subcategories to it. Specifically, after including the necessary columns, I aim to further segment the data from the second row onward into distinct categories. Within the 'cnames' field, there is a list of values like 'canpar,' 'Puro,' and 'UPS.' I have attached an image displaying the current table output on the left and the desired output on the right.

Could you please advise on how to accomplish this?

Priscilla_Parodi · January 30, 2024, 7:13pm

Hello @Shubhankar,

Welcome to the community!

Just to make sure I understand correctly. I see that by comparing one table with the other (expected output), you have the 'carrier' column instead of the 'ship from/to' columns.

Does the 'carrier' field contain values like 'canpar,' 'Puro,' and 'UPS,' and do you want to split the data table by this term? Or do you want a new table with these subcategories?

Have you tried adding a bucket to split rows with a terms aggregation?

Shubhankar · January 31, 2024, 6:32pm

Yes, the carrier field has values like Canpar, Puro, UPS. I have created a table based on ship from and ship to data. Now further I want to divide by carrier too. Below is screenshot, please check. As you can see the first row is based on the From Province and To province from second row it is further divided based on the carriers. Once done then again it show from another From province and to province and then divide that into carrier wise.

Priscilla_Parodi · January 31, 2024, 7:45pm

Are you looking for something similar to this?

github.com/elastic/kibana

[Lens] In table visualization, users can transpose rows into columns

opened 08:27PM - 17 Jun 20 UTC

closed 12:51PM - 11 Mar 21 UTC

wylieconlon

enhancement Team:Visualizations Feature:Lens

Lens users currently define their aggregations in a column-oriented way. Users s…hould be able to select any of their bucketed columns to be transposed for display purposes- this can be used to make the display more dense. This will be most clear with some examples. Here is a relatively small table with 2 buckets and 2 metrics. Because there are 2 buckets, the user can choose the following transpose options: * No transposing * Transpose the first column (date) * Transpose the second column (filters) * Transpose both first and second columns ## No transposing The original data: Date | Filters | Count | Sum -- | -- | -- | -- 3/24/20 | A | 20 | 102 3/24/20 | B | 2 | 23 3/25/20 | A | 3 | 50 3/25/20 | B | 6 | 81 3/26/20 | A | 7 | 97 3/26/20 | B | 8 | 12 ## Transpose the first column Filters | 3/24/20 - Count | 3/24/20 - Sum | 3/25/20 - Count | 3/25/20 - Sum | 3/26/20 - Count | 3/26/20 - Sum -- | -- | -- | -- | -- | -- | -- A | 20 | 102 | 3 | 50 | 7 | 97 B | 2 | 23 | 6 | 81 | 8 | 12 ## Transpose the second column Date | A - Count | A - Sum | B - Count | B - Sum -- | -- | -- | -- | -- 3/24/20 | 20 | 102 | 2 | 23 3/25/20 | 3 | 50 | 6 | 81 3/26/20 | 7 | 97 | 8 | 12 ## Transpose first and second columns <span></span> | 3/24/20 - A | 3/24/20 - B | 3/25/20 - A | 3/25/20 - B | 3/26/20 - A | 3/26/20 - B -- | -- | -- | -- | -- | -- | -- Count | 20 | 2 | 3 | 6 | 7 | 8 Sum | 102 | 23 | 50 | 81 | 97 | 12 # Implementation note I propose that we implement this in _visualization options_ for the table visualization, not as a feature of the Lens datasource or Lens editor. The main reason is that we don't need this for other visualizations, since the visual encoding would be the same. One requirement that we need to keep is the behavior of applying a filter based on a table cell. The semantics of this should be the same, even though the table cells will be presented in a new order. Also, as you can see in the example above, not every transposed table is as dense as others: it's possible that we can recommend the "densest" display of the table by applying some heuristics, such as "suggest that users transpose the column with the lowest expected cardinality"- this will usually not be the date histogram column, but might be the Terms aggregation.

But this depends on the version you are using.

Shubhankar · January 31, 2024, 8:05pm

Those two examples are different. What I am expecting is for eg ON to ON the volume is 14506 as i shared in the first screenshot. This volume consist of all the carriers. Now I want to further byfurcate by carriers for eg, Total volume is 14506 out of this lets assume Canpar has 10000, Puro as 4000, UPS has 400. I am using Kibana 8.12

system · February 28, 2024, 8:05pm

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