Blog Viewer

Create an Excel Matrix with an Excel File and Test the Rule

  

Introduction

An Excel matrix allows you to import data organized as one X and one Y axis to create logic that can return hundreds of results, like a very large decision table. Once imported, you should test it exactly as you would test a decision table to ensure it returns the values you expect it to.

QUICK STEPS
1 Open the entity and expand the Rules section
2 Add a rule
3 Enter the rule details
4 Browse to the file
5 Load the file
6 Enter the details for each sheet you want to import
7 Add the Data Starting Sheet you want to import
8 Click OK
9 Add the Row Parameter
10 Enter the details
11 Click OK
12 Add the Column Parameter
13 Enter the details
14 Click OK
15 Enter the Return Type
16 Click OK
17 Open the newly created rule
18 Build the entity rule
19 Add a scenario
20 Enter the values for the test
21 Run the test
22 View the diagram
23 Save the scenario

Detailed Steps
Step 1. Open the entity and expand the Rules section.

 

Step 2. Add a rule.

Click the + icon and select the rule type. This example creates an Excel Matrix. 




Step 3. Enter the rule details.


Step 4. Browse to the file.

Click the Search button and open the file from a local or network location.




Step 5. Load the file.

If necessary, change the default precision and delimiter settings. S3 automatically sets precision to 6 decimal places and the delimiter character to a comma. If your Excel data doesn’t run to 6 decimal places (or you want to truncate it), change the precision. If the data has any values with commas, change the delimiter to some character that isn’t present on the spreadsheet so the XML source code can express the column values.

This example uses the default precision and delimiter settings. Click the Load button to view details for all the sheets in the file. 




Step 6. Enter details for each sheet you want to import.

Each sheet uses the following properties, from left to right:

  • Sheet Name: This comes from the Excel file and is read-only.
  • New File Name: This is the name of the rule file S3 will create. Each sheet will be its own separate file. You can freely enter text or select an existing file to create a new version.
  • Effective Date: If you want to create a new version of an existing rule, you must give it an effective date.
  • Data Starting Cell: This tells S3 where on the spreadsheet the data starts.
  • Row Parameter: This tells S3 what kind of data determines the row of the intersection. You can think of this as the matrix’s Y axis.
  • Column Parameter: This tells S3 what kind of data determines the column of the intersection. You can think of this as the matrix’s X axis.
  • Return Type: This is the data type of all the X-Y intersections.
  • Default Output Value: Optionally, you can enter a default value outside of all the specific X-Y values.
  • Reset: This button clears its row so you can start over.





Step 7. Add the Data Starting Cell.

Click the … button and select the intersection of the first X and Y values.




Step 8. Click OK.



Step 9. Add the Row Parameter.

This example uses the age of the member. You must enter a name for the parameter, as well as its data type. Optionally, you can enter a description.




Step 10. Enter the Details.

The important properties are:

  • Name: Programmatic ID of the input value.
  • Description: Description text to be displayed.
  • Data type: This is the type of data the row/ column parameter takes in (string, int, etc.).




Step 11. Click OK.




Step 12. Add the Column Parameter.

This example uses the joint annuitant age as a column parameter. This parameter has all the same properties as the Row Parameter.




Step 13. Enter the details.





Step 14. Click OK.



Step 15. Enter the Return Type.

In this example, the Return Type is decimal.


Step 16. Click OK.

S3 displays a confirmation that creating the rule was successful.





Step 17. Open the newly created rule.



Step 18. Build the entity rule.

You must build the rule so it can be called.




Step 19. Add a scenario.

In order to test the matrix, you need to add a scenario.

Once a scenario exists, this icon changes to Navigate to Scenario.


Step 20. Enter the values for the test.

Enter the Input Value and Expected Value for the step.



Step 21. Run the test.

Click the Run Test icon to run the test step.



Step 22. View the diagram.




Step 23. Save the scenario.

This post is part of the Rules topic. Click here to open the Rules Overview.


#Develop
#Rules
#Task Steps
#Everyone
#Text Help

0 comments
33 views

Permalink

https://SagitecDocs.Sagitec.com/blogs/rama-shadija/2022/07/01/import-and-test-an-excel-matrix