1. No category

ADVANCED DATA MORPHING
Advanced Data Morphing
Project parameters
 Parameters can be used instead of file names in
transformations and as constants in expressions:
[Amount] * {Exchange rate}
 Parameters are global for all tables
 Parameter types:
•
•
•
•
Parameter
Number or text
File name – text with file browser button
Date – number with date picker
Calculated parameter – parameter that is calculated using
an expression
Parameter Editor
Type: File Name
Type: Date
Calculated
parameter
Choose
parameter type
Parameters in expression editor
List of parameters lives here
Project parameters (continued)
 Calculated parameters can’t refer to other calculated
parameters in order to avoid cyclic dependencies
 Non-calculated parameters are prompted on full runs:
• On Run after a project is opened
• On Reload and Run
 Parameters can be defined when running a project in
command line mode. Examples:
morph.exe /c /run /param:Year=2015 proj.morph
morph.exe /c /run /param:”File name”=“C:\My
documents\file.csv” proj.morph
Iterations
 Iterations in EM are done by running another EM
project and passing to it all values of a column
through a parameter, one row per iteration:
iteration #1
Project B
Run with
Param1 = aaa.txt
iteration #2
Project B
Run with
Param1 = bbb.txt
iteration #3
Project B
Run with
Param1 = ccc.txt
Project A
Table 1
File Name
aaa.txt
bbb.txt
ccc.txt
Project A runs Project B 3 times, assigning its Param1 with values from [File Name]
Iterate transformation
 Used for iterations in EasyMorph
 Two iteration modes:
• Iterate – run another project N times, return nothing (the
other EM project is supposed to have at least one export
transformation)
• Iterate and Append – run another project N times, return N
final states of the specified table in that project, and
append all of them into one table which becomes the
output of Iterate transformation
 The project to run can be specified by a parameter
Iterations (2)
 Iterations are very versatile:
•
•
•
•
Processing a list of files, or a sequence of dates
Cycles (Param = 1, 2, … N)
Nested iterations (A runs B that runs C)
Reusable logic (A runs C, B runs C)
 General design pattern: prepare a list, then iterate
 Pass more than one parameter if needed:
{ParamA} = [File Name]
{ParamB} = [Year]
 Not assigned parameters have default values
Transformations for list creation
 Iteration lists can be loaded from external files or
created using various transformations:
Calendar – generates a list of dates for specified time
period, including optional columns such as day of week,
weekend flag, etc.
File List – generates a list of files in a specified folder,
including optional columns such as file size or date
created
Folder List – generates a list of folders in a specified folder
Sequence – generates a table with one column with
numbers from 1 to N
Parsing complex spreadsheets
 Identify boundaries of tables on a sheet
Table 1
Table 3
Table 2
Sheet1
Parsing complex spreadsheets (2)
 Use Derive Table to branch calculations
Parsing complex spreadsheets (3)
 Crop identified tables using Trim Table and Select
Columns transformations
Select Columns
Trim Table
Table 2
Parsing complex spreadsheets (4)
 After cropping keep transforming further
Parsing complex spreadsheets (5)
Useful transformations for dealing with spreadsheets:
Trim Table and Trim Table By Condition – remove
unwanted rows below or above
Fill down – fill empty cells down with nearest nonempty value in the same column
Unpivot – turn matrix table into a straight table
Label Columns – make column names from one or
more top rows
Error propagation in expressions
 If a part of an expression evaluates to an error then
entire expression returns the error
• Simpler debugging of complex expressions
 Reference to an error in another column is also an
error
 Some functions don’t have error propagation (e.g.
iserror, istext, etc.)
• See full list here
http://help.easymorph.com/doku.php?id=syntax:errorprop
agation
Conditions
 Conditions – expressions that must return only either
TRUE or FALSE for any row
 Any other value stops project execution with an error
 Used in transformations:
• Filter by condition
• Trim table by condition
 To deal with errors in column values, remove errors
first (e.g. using iserror ) and only then apply
conditions
easymorph.com