Here is the sample. Delta column is what I am trying to acheive. Part# are category.
Part# | Data | Delta |
1 | 4 | 4 - 4 |
2 | 5 | 5 - 5 |
3 | 6 | 6 - 6 |
1 | 7 | 4 - 7 |
2 | 8 | 5 - 8 |
3 | 9 | 6 - 9 |
1 | 10 | 7 - 10 |
2 | 11 | 8 - 11 |
3 | 12 | 9 - 12 |
Do I need to define "Delta Pattern" column?
As a column formula
If( Row() <= 3, :Data, :Data - Row() );
In a Script
For Each Row(
If( Row() <= 3, :Delta = :Data, :Delta = :Data - Row() );
);
Hi Jim,
thanks for prompt response.
this works in case the row number lines up with values in Data column for subtraction. what if there are random values in Data column and want to consider the raw values of data columns for subtraction?
Sample data
Part# | Data | Delta |
1 | 0 | 0 - 0 |
2 | 0 | 0 - 0 |
3 | 0 | 0 - 0 |
1 | 9 | 0 - 9 |
2 | 23 | 0 - 23 |
3 | 45 | 0 - 45 |
1 | 1 | 9 - 1 |
2 | 34 | 23 - 34 |
3 | 56 | 45 - 56 |
Here is how I would do it
Names Default To Here( 1 );
// Create the sample data table
dt = New Table( "Example",
Add Rows( 9 ),
New Column( "Part#", Character, "Nominal", Set Values( {"1", "2", "3", "1", "2", "3", "1", "2", "3"} ) ),
New Column( "Data",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 9, 23, 45, 1, 34, 56] )
),
New Column( "Delta Pattern",
Character,
"Nominal",
Set Values( {"0 - 0", "0 - 0", "0 - 0", "0 - 9", "0 - 23", "0 - 45", "9 - 1", "23 - 34", "45 - 56"} )
)
);
// Add a new column to provide a path back to the original order
dt << New Column( "RowNum", formula( Row() ) );
// Delete the formula, so the values become static rather than calculated
dt:RowNum << delete formula;
// Sort the data by Part# and Row
dt << sort( by( Part#, :RowNum ), replace table( 1 ) );
// Create the new column with the delta values, which is pretty simple with all of
// the data sorted by Part#
dt << New Column( "Delta", formula( If( Lag( :Part# ) != Part#, 0, Lag( :Data ) - :Data ) ) );
// Delete formula so the values become static
dt:Delta << delete formula;
// Sort back into the original order
dt << sort( by( :RowNum ), replace table( 1 ) );
// Delete the Rownum column since it is no longer needed
dt << delete columns( "RowNum" );
There are otherways to do it too, but they would be slower to calculate
Do I need to define "Delta Pattern" column?