External Operations = Data Link
🚨 Pre-requisite : Resulting index
The Resulting Index is the first operation to create in a Child or Output table. It is thus used to indicate the destination index of the next operations among copy, group by, group by split and pivot.
Copy
Copying simply makes a copy of one column (or more) to one or more other(s).
A Copy is created in a Child or Output table (not available in an Original table)
It's possible to create several copies in the same table.
If the index of each copy is identical, values will be copied in the same line.
If the indexes are different, values will be copied in new lines .
External copy allows you to copy a full table from another Mx.
Only for Braincube internal use, see documentation on dedicated Google Site.
Pivot
A pivot runs like this:
To create a pivot (or an original pivot) you need to specify:
- A tag column, which will create the columns of the array
- An index column
- A value column
You can create several (original) pivots in the same table, as long as the index is always the same.
A pivot is created in a Child or Out table.
You can add an additional variable: it should have always the same value for all rows of each index, be aware that MX takes the last value.
If you want to have more additional variables, you have to create an extra copy of these variables from your source table (index = the same as the pivot)
Group By
The "Group by" is used to aggregate data from a source table to a destination table.
For example, to aggregate data from the source table "Caldera_HF_Table" to destination table "Caldera_10min_Agg", we will create the Group By operation in the "Caldera_10min_Agg" table, and Aggregation index will be a variable "DateTime_10min/in.java" created in "Caldera_HF_Table"
It means that 10 minutes of data from "Caldera_HF_Table" will be aggregated in 1 line in "Caldera_10min_Agg".
The result of aggregations depends on the type of data.
- Copy, average, max, min, first, last and median: return the same type of data as the input type.
- Sums, standard deviation and maximum of the absolute value: return numbers and are impossible for dates.
- Average, sum, standard deviation and maximum of the absolute value: return "" for strings when there are no numbers.
- Counting, slope, max-min and last-first always return numbers.
- Concatenations always return strings.
- Values: return all modalities separated by "|" like this : c|a|b|b|a|b|b|c. You can clean values into a “a, b, c” list for example (Sorted list of unique concatenation occurrences in Braincube Academy) or something else.
Group By split
Runs a Groupby then a split, equivalent to a pivot. It is created in a new table, same operations are available.
Example: A heat treatment operation with a first rise to a first step, then a second rise to a second step.
We just need to construct a splitting variable "phase/in.java" that returns R1, S1, R2, S2 depending on the moment in the cycle, then run an aggregation by cycle number, split by phase.
In one operation, the whole cycle is described.
Additionally, to the large time gain, the columns are created dynamically, so if the phase list evolves, everything is automatic.
Other use: A table containing defects with quantities in a column and a family in another column. Instead of aggregating then pivoting, everything is done in a single operation.