Defining new input tables

Follow these steps to add a new table that was added to your input data.

Note:

Make sure the input data needed for your new table is available in your app. See also Loading data.

1. Go to the Data transformations editor.

2. Add the new input table to the sources.yml file.
   
   

   

   
   

3. Add a new file for the input table in the models -> 1_input section of the Transformations.

4. Add the fields in the select statement.
   Tip:

   Use the pm_utils.mandatory and pm_utils.optional macros to define mandatory and optional fields from the pm_utils library.

   pm_utils.mandatory	Loads and type-casts a column. If the source column does not exist, an error is thrown.
   pm_utils.optional	Loads and type-casts a column. If the source column does not exist, a column is created with value NULL.

5. For each field:

   1. Set the correct field type. For example , 'double'.
   2. Name the field using an alias. For example as "Case_ID".

      Below is an example of a table definition.

      /* Some fields in this table are optional. These fields are created in the SQL if they do not exist in the source data. */ 
      with Cases_input as ( 
         select 
            -- Mandatory 
            {{ pm_utils.mandatory(source_table, '"Case_ID"') }} as "Case_ID", 
            -- Optional 
            {{ pm_utils.optional(source_table, '"Case"') }} as "Case", 
            {{ pm_utils.optional(source_table, '"Case_status"') }} as "Case_status", 
            {{ pm_utils.optional(source_table, '"Case_type"') }} as "Case_type", 
            {{ pm_utils.optional(source_table, '"Case_value"', 'double') }} as "Case_value"
         from {{ source_table }} 
      ) 
      
      select*from Cases_input

To make the new fields available for use in your dashboards, the fields must be added to an output table. See Adding fields for a description on how to create output fields.

Field types

Below is an overview of the different field types and their default format settings.

Field type	Format
boolean	true, false, 1, 0
date	yyyy-mm-dd (default)
datetime	yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional. (default)
double	Decimal separator: . (dot) Thousand separator: none
integer	Thousand separator: none
text	N/A