Parametric Selection Feature

In the previous example, all data used in the model was simply passed as constants in the file. The example model DT Loan Strategy Model2.json, demonstrates how to import this same data from an external data file using a parametric selection criteria. A Parametric Selection allows a single record to be selected from an external datasource file as an input. Parametric selection in data-sources is universal, it is critical to decision tables, which expect a single record for their inputs. All supported data types may be used with this feature.

Most of the customer and loan data is imported from the two datasources: loan_data and cust_data. The datasources section creates two datasources, cust_data and loan_data. The datasource, cust_data, binds to the customers.txt csv file. This file contains five input parameters, age, maritalStatus, employmentStatus, creditScore and bankrupt. A screenshot of this file is shown below.

The loan_data datasource binds to the loans.txt csv file. This file contains three input parameters: type, rate and term.

      
{ 
  modelName: "DTLoanStrategyExamplewDatasource",
  modelType: "calculation",
  modelDescription: "Loan Strategy model",
    datasources :{
      cust_data:  { 
        type: "csv", 
        connection: "customers.txt", 
        selection: "custID = ?", 
        parameters: {
          cuID: { 
            binding: 'get', 
            value: 'c1'
          }
        },
        indexCols: ['cust ID'], 
        valueCols: ['age' 'maritalStatus', 'employmentStatus', 'creditScore', 'bankrupt'] ,
        direction: "import"
      },
      
  

Inside of the cust_data datasource, we see the connection argument passing the CSV file, connection: "customers.txt" (screenshot above). The selection argument selects the "CustID" column , from the customers.txt file, and replaces "custID = ?" with "custID = cuID"; the parameters argument binds "cuID" to "c1". In addition, indexCols is set to "cust ID" and valueCols are set to 'age', 'maritalStatus', 'employmentStatus', 'creditScore' and 'bankrupt'. This means that cust ID is the index column and 'age', 'maritalStatus', 'employmentStatus', 'creditScore' and 'bankrupt' are the value columns.

    
      loan_data:  {
         type: "csv",
         connection: "loans.txt",
         selection: "loanID = ?",
         parameters: {
            loID: { binding: 'get',value: 'l1' }
        },
        indexCols: ['loanID'],
        valueCols: ['type', 'rate', 'term'],
        direction: "import"
      }
    },
    
  

Inside of the loan_data datasource, we see the connection argument passing the CSV file, connection: "loans.txt" (screenshot above). The selection argument selects the "loanID" column , from the loans.txt file, and replaces "loanID = ?" with "loanID = loID"; the parameters argument binds "loID" to "l1". In addition, indexCols is set to "loanID" and valueCols are set to 'type', 'rate' and 'term'. This means that loanID is the index column and 'type', 'rate' and 'term' are the value columns.

If we wanted to use multiple selection criteria, such as cust ID, maritalStatus and age, we would change the code to the following:

     
       datasources : {
         cust_data:  {
           type: "csv",
           connection: "customers.txt",
           selection: "custID = ? and maritalStatus=? or age=?",
           parameters: {
             cuID: { binding: 'get', value: 'c1' },
             marry: {binding: 'get', value: 's' },
             yrold: {binding: 'get', value: '44' }
           },
           indexCols: ['custID'], valueCols: ['age', 'maritalStatus','employmentStatus', 'creditScore', 'bankrupt'],
           direction: "import"
         },
     
   

The selection component indicates which column or columns in the database/table are to be queried. There is no limit on the amount of parameters queried using "and" or "or".

In this example, the "binding" property allows cuID, marry and yrold parameters to be queried outside of the RASON Model environment (see example below); 'c1’ is the default custID in this example; 's' is the default maritalStatus and '44' is the default 'age'; if a query parameter is used outside of the RASON model environment, this value will be replaced by the query parameter.

Parameters may be matched to the selection arguments "by order" or "by name". In the example code above, the parameters are matched to the selection arguments "by order". This means that the order in which the selection arguments are given must match the order of the parameters i.e. the selection argument order in the example above is custID, maritalStatus, age; therefore, parameters must be listed in the same order: cuID, marry and yrold. If matching by name, the order is not relevant. An example of how to match by name is below.

Here, "custID" is matched with "cuID", "maritalStatus" is matched with "marry" and "age" is matched with "yrold" because they have been explicitly matched.

Note: A parameter must have a different name from the column name in the table to which it refers. In the example above, we use "cuID" as a parameter to distinguish it from the column name "custID" because "custID" is a column name marked as an index column. Index columns are index set objects used in the model to dimension variables. They cannot be mixed with parameters. The parameter "cuID" is a single value, an element of a table column used to select records. However, "custID" is the set of all possible elements of a column.

• indexCols indexes the data first by the custID column
• valueCols defines the value columns age, maritalStatus, employmentStatus, creditScore and bankrupt.

To perform a query using custID, maritalStatus and age outside of the RASON model environment, use the query parameter:

Or in general,

The RASON Server will map "custID=?" with "custID = c2", "maritalStatus=?" with "maritalStatus = s" and "age=?" with "age=44" .

The remaining data is passed in the data section. The input data custExist is passed as a constant within the RASON model.

  
    data: {
      comment: "use binding to feed dif. values",
      custExist: { value: false },
      custAge: { 
        value: 40,  
        binding: 'cust_data', 
        valueCol: 'age' 
      },
      maritalStatus: { 
        value: 's', 
        binding: 'cust_data', 
        valueCol: 'maritalStatus' 
      },
      employmentStatus: { 
        value: 'selfEmployed', 
        binding: 'cust_data',
        valueCol: 'employmentStatus' 
      },
      creditScore: { 
        value: 610,   
        binding: 'cust_data', 
        valueCol: 'creditScore' 
      },
      bankrupt:{ 
        value: false, 
        binding: 'cust_data', 
        valueCol: 'bankrupt' 
      },
      monthIncome:   { 
        value: 2500, 
        binding: 'get' 
      },
      monthExpenses: { 
        value: 1000, 
        binding: 'get' 
      },
      loanType: { 
        value: 'standard', 
        binding: 'loan_data', 
        valueCol: 'type'
      },
      loanRate: { 
        value: 5.0, 
        binding: 'loan_data', 
        valueCol: 'rate'
      },
      loanTerm: { 
        value: 30,  
        binding: 'loan_data', 
        valueCol: 'term' 
      },
      loanAmnt: { 
        value: 100000.0, 
        binding: 'get' 
      }
    },
  

It's also possible to match the name defined in the RASON model to the SQL parameter name. In this example, the syntax would be:

      
      cust_data:  { 
        type: "csv",
        connection: "customers.txt",
        selection: "custID = $cuID",
        parameters: {
          cuID: { binding: 'get', value: 'c1' },
          marryStat: {binding: 'get', value: 's'}
       },
       
     

Or, with multiple selections…

      
        cust_data:  {
          type: "csv",
          connection: "customers.txt",
          selection: "custID = $cuID and maritalStatus=$marryStat",
          parameters: {
            cuID: { binding: 'get', value: 'c1' },
            marryStat: {binding: 'get', value: 's'}
          },
      
    

The remaining data is passed in the data section. The input data custExist is passed as a constant within the RASON model. Input data monthIncome, monthExpenses and loanAmnt are passed within the RASON model as "get" only. This binding property allows write access to the data outside of the model environment using the keyword "get".

The decision table results return the recommended loan strategy for customer 1.

        
      {
        "status": {
          "code": 0,
          "id": "2590+DTLoanStrategyExamplewDatasource+2020-03-02-18-43-04-186842",
          "codeText": "Solver has completed the calculation."
        }
        "observations": {
          "strategy": {
            "value": "bureau"
          },
          "routing": {
            "value": "accept"
          }
        }
      }