Support FAQ

Plotting Bar Chart Side-by-Side

In this example, we will be plotting a grouped bar chart to show the GDP per capita for UK, USA, JPN and AUS between 2017 and 2020. The result looks as such:

To start off with, create a Datamodel with the code snippet below. Once we have a table with our data, in this case GDPPerCapita table we need to prepare the dataset to plot the data. The following code snippet prepares the data by creating a table called GDPPerCapitaPlot.

{
 create table GDPPerCapita (Year string, UK double, USA double, JPN double, AUS double);
 insert into GDPPerCapita values ("2017", 45000, 58000, 42000, 50000);
 insert into GDPPerCapita values ("2018", 50000, 55500, 42500, 46000);
 insert into GDPPerCapita values ("2019", 47500, 59500, 45500, 42700);
 insert into GDPPerCapita values ("2020", 42500, 54700, 43500, 45700);
 
 Table t = select * from GDPPerCapita;
 List yearList = select Year from GDPPerCapita;
 List valTypesList = t.getColumnNames();
 valTypesList.remove(t.getColumnLocation("Year"));

 create table GDPPerCapitaPlot (Year string, valType string, x double, val double);
 
 int n = 1;
 for (int i = 0; i < yearList.size(); i++) {
    Row r = t.getRow(i);
    string Year = r.get("Year");
    double ld = 0.0;
    int cnt = 0;
    for (string valType : valTypesList) {
      double val = r.get(valType);
      insert into GDPPerCapitaPlot values (Year, valType, n, val);
      ld += n;
      n += 1;
      cnt += 1;
    }
    ld = ld / cnt;
    insert into GDPPerCapitaPlot values (Year, "label", ld, 1.0);
    n+=1;
  }
}

Next, we will add a visualisation to the Datamodel on the GDPPerCapitaPlot table. Choose the ‘2D Chart’ – ‘Advanced’, and fill in the options as below (For ‘Color’ in ‘MARKERS’ section choose Custom_Series and add series of colours in hex code for example ‘#E4572E,#29335C,#F3A712,#A8C686,#669BBC’):

This will give us the following bar plot:

To add axis and get the final result, add a panel below the bar chart, then add a visualisation based on GDPPerCapitaPlot table with the following information:

Editing and styling the axis and the divider gives us the desired chart.

Inserting Data from Datamodel to AMI DB

In this example, we will show how to insert data from one datasource to another by using the Datamodel. Here the Country table exists in a datasource called WORLD. We want to take this table and create a copy of it in AMI datasource. To create a copy of the Country table, we need the schema which we get using the DESCRIBE clause.

{
  CREATE TABLE Country AS USE  EXECUTE SELECT * FROM `Country` WHERE ${WHERE};
  string s = SELECT SQL from DESCRIBE TABLE Country;
  s = strReplace(s, "CREATE TABLE", "CREATE PUBLIC TABLE IF NOT EXISTS");
  // session.log(s);
  USE ds="AMI" LIMIT = -1 EXECUTE ${s};
  // USE ds="AMI" EXECUTE DELETE FROM Country;
  USE ds="AMI" INSERT INTO Country FROM SELECT * FROM Country;
}

Firstly, create a Datamodel that is attached to the WORLD Datasource. Copy and paste the above script and run. This will now give you the Country table in the AMI datasource.

Dynamic Aggregation on Visible Columns

In this example we go through the steps showing how a user can use visible columns to drive aggregate columns. This is done by getting the visible columns and using the column names to drive a new query whenever the columns are hidden or shown.

We start by creating a datamodel that produces a sample table with some data using the following code:

{
  CREATE TABLE Sample(`Id` int, `Country` string, `Sz` long, `Px` double, `Ordtype` string, `Side` string);
  INSERT INTO Sample Values(1,"HK",5000,3.00,"1","B");
  INSERT INTO Sample Values(2,"HK",3000,5.00,"2","B");
  INSERT INTO Sample Values(3,"CH",2000,6.00,"1","B");
  INSERT INTO Sample Values(4,"CH",1500,8.00,"2","S");
}

Create a blender on the previous datamodel. Add an argument into the ‘onProcessArgs’ this will be used to contain the column names that will drive the aggregation:

Use the following code to produce a new table that will have some aggregate columns, in this example we have a column that sums all of the sizes and a column that gives the average price.

We want to add some conditions using an if statement, this is where we make use of the ‘onProcessArg’ we defined previously. Since this argument will contain the column names that drive the aggregation we want to make sure that a table is still created even if the argument is empty. We do this by adding a condition to create a table from our existing columns if no new column is provided.

If we are given a new column name we add this to a select statement. We add the column name both in the select portion and then at the end to group the results of the query.

{
  if (groupByClause == "" || groupByClause == null) {
    //groupByClause = "Country, Ordtype, Side"; 
    create table CustomAggSample as select sum(Sz), avg(Px) from Sample;
  }
  else {
     create table CustomAggSample as select ${groupByClause}, sum(Sz), avg(Px) from Sample group by ${groupByClause}; 
  }
}

When columns are hidden or shown whichever column is furthest to the left drives the top level of aggregation and then each column to the right drives the next highest level and so on (not including the aggregate columns).

Open the custom callbacks from the following menu:

Within the ‘onColumnsArranged()’ paste the following code. This code first gets a list of all of the visible columns. Then we remove our aggregate columns, in this case we want to remove the sum of the size and average price columns, these are removed as they are not used in the aggregation but instead just show the results of the aggregation. We add each visible column name to a map m. We process our original datamodel and provide our map as the parameter, this map will be parsed to the ‘onProcessArg’ called ‘groupByClause’ that we defined earlier.

{
  list visCol = this.getVisibleColumns();
  list visColProcessed = new List();
  for (string c : visCol) {
    if (!strStartsWith(c, "avg", true) && !strStartsWith(c, "sum", true)) {
      visColProcessed.add(c);
    }
  }
  string groupByClauseStr = strJoin(", ", visColProcessed);
  //session.alert(groupByClauseStr);
  Map m = new map();
  m.put("groupByClause", groupByClauseStr); 
  multi1.process(m);
}

Finally hide or show columns as shown in the images below and watch the aggregation happen in real time.

We can see the results of hiding and showing the columns in the images below.