Taylor’s Corner: Let’s Learn – Calculated and Custom Columns

Calculated Columns

The Calculated Column node allows users to make changes to columns in the selected tables during the data flow process using a PQL script. After the node has been added in from element bar and connected to the selected tabled, users will either write or provide a PQL script into the script window to manipulate the column. Once the PQL script has been provided, columns can be changed to perform calculations, create new or manipulate existing column values, etc.

Let’s work in the Modeling module. Once your data source and tables have been added to your canvas, click “Column Operators” and add the calculated column node and connect it to the needed table.

 

 

In the Properties table, write or provide your PQL script in the Script window. You can also select the PQL editor button (red arrow) to open the advanced script window to write your expression.

 

 

For this example, we are going to keep it simple. I have created a custom column with the values of “Test” (blue highlight). I have decided to add a new column at the end of the table instead of replacing a previously written column (red arrow). I then will change the name of the column to “Testing Column” and select the column type (yellow highlight).

 

 

Click the preview icon to preview the new calculated column.

 

 

 

Custom Columns

Custom Columns, a new feature of Pyramid 2023, are calculations that allow the user to change calculated values of an existing data model as new columns of data. With customs columns, user can create new measures and hierarchies. This means that users can extend the calculations of a model at a low level to fit any requirements needed. Custom Columns can be constructed to either the data model or in formulate.

In this example, we are going to take our demo data and create a column that presents loss data by city and state in a single dimension.

Opening our data model, we are going to open the Columns tab and click “Add Column Calculation.”

 

 

The Custom Column window will appear. Selecting the “String Blocks” from the tool-panel, we are going to utilize the “Concat” feature which will join the values of both columns into a single string.

First select “Loss City” from the list as the first model attribute.

 

 

I will simply insert a “,” as my fill in argument, as this is how I would like to separate my data in the single dimension.

 

 

The select “Loss State” as the second model attribute and close out the Concat formula. In the Properties window, we can change the column name, I have change to “Loss City & State” (blue highlight) for this example. Display folder, sort and category type can also be selected from this window.

 

 

Clicking on the Preview button (blue highlight), I can see that a new column labeled “Loss City & State” (red highlight) that joins both values have been added into a single dimension.