Usercase 3 : Flexible Age Range Configuration for Data Analysis

Reading time:

This function is for advanced editors only.

Use case: Data often needs to be reformatted for easier interpretation, such as displaying age ranges instead of exact ages in statistics. Since these ranges may change based on the analysis, allowing customers to define age ranges using an editable grid meets their varied needs.

Step 1: Create an age range Table in ClickHouse

Begin by creating a table in ClickHouse to store age ranges. It is important not to use the 'log' engine; instead, use 'MergeTree' as the engine for this purpose. Below is an example SQL statement for creating the custom_age_range table:

CREATE TABLE custom_age_range
(
  `ID` UInt64,    
  `min_age` Nullable(Int8),
  `max_age` Nullable(Int8),
  `Description` Nullable(String),
)
ENGINE = MergeTree
PARTITION BY ID
ORDER BY ID
SETTINGS index_granularity = 8192;

Step 2: Visualize the Table in BI4H

Navigate to BI4H visual and add a simple grid to a page. Populate this grid by including all fields from the custom_age_range table.

Step 3: Configure Editing Capabilities

  1. Enable Allow Editing and set ID as the Primary key in the Edition section.
  2. Enable Allow Editing for the min_age field in the Edition section.
  3. Enable Allow Editing for the max_age field.
  4. Enable Allow Editing for the Description field.

Step 4: Enable Grid Interactivity

After configuring the fields, proceed to the interactivity tab. In the editing section, enable the following options:

  1. Allow Updating
  2. Allow Adding
  3. Allow Cloning
  4. Allow Deleting
  5. Set Batch as the editing mode

Step 5: Create a Dictionary to Identify Patient Age Ranges

To facilitate the identification of which age range a patient belongs to, create a dictionary. This dictionary will map specific text values to relevant parameters.

CREATE DICTIONARY IF NOT EXISTS age_range_dict
(   
    ID UInt16,    
    min_age UInt16,
    max_age UInt16,                 
    Description String
)
PRIMARY KEY ID
LAYOUT(range_hashed)                
RANGE(MIN min_age MAX max_age) 
SOURCE(CLICKHOUSE(TABLE 'custom_age_range' USER 'chAdmin' PASSWORD 'b14h@d3dCH' DB 'bi_dpi'))
LIFETIME(MIN 300 MAX 600);

 

Step 6: Create an Executable Query Snippet to Refresh the Dictionary Immediately After Changes

When modifications are made in the table, there may be a delay before the linked dictionary is updated. To view changes immediately, you can execute a query snippet to refresh the dictionary. 

Step 7: Build a Widget Based on the Dictionary

After setting up the dictionary and ensuring it is refreshed as needed, the next step is to build a widget that utilizes this dictionary. This widget will leverage the dictionary to display or process patient age range information as required.

A video to show you how to use it.



Last update: 4 February 2026
Views: 53