Now we want to transpose this table so that the column headers are the different bill types and the rows contain the amount for each house. Thus since there are 4 houses, we will get a dataset with 4 rows. By using the following script we can generate our desired output:
We first create a datamodel on the HouseBills table that is stored in AMI. Next we extract the distinct houses names and bill types, and store it in a list named HouseName and billType respectively.
We will need to create a schema for the new transposed table - to do this we use unique list containing the bill type and perform a strJoin by adding the data type of those columns (in this case we assign their column type as a float). So the columnNameSchema string is:
This can now be used to create the TransposeTable.
Finally we want to get the amount corresponding to each house and bill type so we perform a for-loop that extracts the required amount and appends it to the string insertVals. Note if the amount for a particular bill type doesn't exist then we will assign a null value. Once we have the string of values to insert in the correct format we can insert it into the transpose table. We get the final result as such:
Next we want to create a callback such that every time we click on a values in a row it will generate the display table (table in the right panel above). To do this we will use the onCellClicked(column,val,rowval) callback under amiscript callbacks for the table panel. The following script will get the row values and the corresponding column names and save it in the table.
TableRowValTable=newTable("RowValTable","ColumnName String, Value String");for(stringc:rowvals.getKeys()){stringelem=rowvals.get(c);if(!strStartsWith(c,"!",true)){RowValTable.addRow(c,elem);}}mapm=newMap();m.put("RowValTable",RowValTable);displayTable.processSync(m);
Enable the displayTable datamodel from the Variable Tree on the right so that the script knows what datamodel to process:
Now we need to make the columns clickable so choose one column (or any number) and under edit column turn on clickable.
Test this and then create a visualisation on the display table in the right panel. Now to have the datamodel containing the display table to update with the row values from the main table, we will update the displayTable code snippet to: