In some cases, you may want to keep the definitions of your workflow expressions in an external file. Here is an example that demonstrates how such definitions can be imported and used in EasyMorph using the eval() function.
expression-list-eval.zip (8.5 KB)
Note that expression definitions must be passed as parameters. They can't be passed just from column values because an expression is constructed before it's calculated for the entire column.
Thank you for the insight.
What if we want to bring in several formulas directly from the spreadsheet? For example,
Var Name | Formula |
A | [B] + [C] |
B | [D] + 2 |
C | Exp(3) |
D | 2^4 |
Is this possible? The transparency of the formulas is a key issue. Thanks.
Hello @yuntienlee and welcome to the community!
In the previous downloadable example there are multiple (2) formulas being brought in from the spreadsheet. What do you mean with "brining in several formulas directly from the spreadsheet"? Can you give more information on your example with the 4 variables, and what you're trying to achieve?
Thanks!
Yes, what i would like to do is to bring in all 4 variables A,B,C,D into the model directly, and each may have reference to other variables in the list instead of pure arithmetic expressions. You may see in the example that A is determined via B and C and B via D. Hopefully you can share some insights, thanks.
In order to achieve this, you need to reference the other expressions as parameters (with curly brackets) and wrap them in eval().
This is how the expression list in the Excel would look like in your example:
| Expression name | Formula |
|---|---|
| A | eval({B}) + eval({C}) |
| B | eval({D}) + 2 |
| C | exp(3) |
| D | pow(2,4) |
Once passed as parameters, they are evaluated using the eval() function (which also evaluates the nested eval() included inside the formula), obtaining the 4 results.
I attach the updated ZIP with the EasyMorph project + excel with expression list:
We've made a few changes in EasyMorph to make it possible to not use eval() in the formula definitions in the Excel file, so the formula definitions can be cleaner. If you get the latest version from our website, the example below will work too:
expression-list-eval.zip (9.5 KB)
These are great ideas, but bear with me another question. What if [C] and [D] are not single numerical values but instead are numerical columns from an input table?
Can you provide an example of "not single numerical values but instead are numerical columns from an input table"? Where would this input table come from?
Yes, for example, [C] and [D] are both numerical columns from a node that brings in data from a CSV file.
What are you referring to with "node" in this context? Can you provide a full example of what you are trying to achieve? Thanks!
I am not sure how to build one which is why i asked here. Basically a node is a component on the workflow canvas, so i would like to use an Excel component to read in data including numerical columns [C] and [D], and use another Excel component to read in all formulas including [A] = [B] + [C] and [B] = [D] + 2, and use an output component to get new results in columns [A] and [B].
Thanks for the information I attach the following zip file, which includes:
expression-list-eval-v2.zip (16.9 KB)
"Main" module (parent module) - here the "expressions.xlsx" file is opened, and the expressions are sent as parameters to module "Calculate columns", which is the child module.
"Calculate columns" module (child module) - here we are loading the "Numerical columns.xlsx" file, which includes two columns with numerical values (not formulas, just the numerical value). After this import, columns A and B are calculated, bringing the expression as a parameter from the parent module, while the values of the expression are obtained from the columns in this table. This result is then passed back to the "Main module" in the "Call another module/project" action.
Are you referring to this?
Install the latest version from our website here: Download free ETL tool | EasyMorph
We made a few changes yesterday to enable this scenario.
That works, thanks. To push this further, I noticed we also have to define A and B in parameters section in Calculate columns module. Are there any ways to auto detect columns to add to parameters? For example, when adding a new expression [E] = [A] * 3 to expressions.xlsx, the module can add E by itself without the user having to define E in parameters section?
No, this has to be manually edited in the workflow. Apart from the adding the parameter in the "Calculate columns" module, in the "Main" module you need to add the new column "E" to the mapping with parameter "E":
And in the "Calculate columns" module, in the actual calculations, you would also need to add column "E", so that it appears in the table:
So manual steps are necessary here.
Hello @yuntienlee after review, it is possible to calculate additional columns just by adding a new expression to "expressions.xlsx" and not having to modify the project settings.
This is achieved with the combination of two actions: Repeat and Iterate table. You can also find more information in the loops and iterations tutorial.
Note: in order for this solution to work, the order of expressions in the list is important. Expressions must only refer to previous expressions (that are higher up in the list).
I have added a new Expression F, that references expressions B and E (and that are higher up in the list):
Solution
For this solution, we need 3 modules, which I will explain as we get to them:
The first step is, in the "Main" module, to import the numerical columns (unchanged file):
Next comes the Repeat action:
The Repeat action is going to keep running module "Add next expression" until the result table "Next expression" is empty. Once "Next expression" is empty, it will return the last not empty result to the "Main" module, which is the calcuation of all the columns based on the expression list. In the first iteration of module "Add next expression", the input dataset will be Numerical columns "C" and "D". In subsequent iterations, the input dataset of the iteration will be the result of the previous iteration. Here is a diagram of how data is passed between iterations:
In the following step we're trimming the table to keep only the first row, and next we're using the Iterate table action to send these values as parameters (along with the "Input data" table) to the module "Apply expression", where column B will be calculated. We need to send these as parameters so that the eval() expression works in the this child module.
The "Apply expression" module, on one hand, creates a 1-line table with module parameters ("New column name" is a helper table to give the correct header to the calculated column), and the "Result" table uses again the Input action to bring the input data from the parent module. Then, the "Calculated column" (in this case, "B"), is calculated and renamed with the helper table:
This completes the first "Repeat" iteration. Because the "Next expression" table in module "Add next expression" is not empty, this module will iterate again. In the next iteration, the input data will now be columns C, D and B, and now the Keep/Remove matching action will remove column B from the list, as it's already present:
Eventually, once columns A, E and F are also calculated, the Keep/remove matching action will remove all expressions from the list in the "Next expression" table, leaving it empty, ending the Repeat iteration:
And as we had configured the Repeat action to "Return the last non empty result", its result is the table with all the calculated columns:
I attach the project here: expression-list-eval-v3.zip (17.8 KB)
Let me know if you have any questions.
Fantastic. This is very informative, thank you. May I add another module that takes in a list of expressions in any order, maybe with the help of an additional order field and output a list of the same expressions but in the proper order like your example higher up in the list? For example, I can input the list in the order of A-B-E-F and the module automatically detects and finds out the order should be B-A-E-F and output it? In this case I do not have to worry about the order in the input list and EasyMorph can determine the proper order and do all the calcs.
Hello @yuntienlee yes, this is possible using the Regular expression action in the "Add next expression" module.
Let's say that we've put expression "F" ([B] + [E]) in the first position of the expressions:
I attach a screenshot of how the module looks now. It includes the following tables:
Input data - same as before
Existing columns - same as before
Candidates - same as first two actions from table "Next expression" in the previous version - removing from those expressions whose columns are present in the input data.
Blocked - this table will list those columns that still can't be calculated, as their referenced columns are not yet in the input data.
\[([^\]]+)\] and we're interested in finding all matches, and capturing only the groups. If you need more details on regular expressions, you can check this link. This allows us to capture the column names found inside square brackets and list them in the newly created column "Referenced column". If there are references to multiple columns in a single expression, this will create new lines, such as is the case for expression name "F" and "A".And the iterations will continue in the correct order until all columns are calculated.
I attach the project here: expression-list-eval-v4.zip (18.0 KB)
Let me know if you have any questions.
Thank you for the quick reply. This model is perfect for our projects. Just one more question, is it possible to add detections of self references and circular references when doing the Add next expression module?
