How to auto copy if column's data is missing?(script)

JimmyWang

I need to daily update this table(as below)

I will update  new Product ID & recipe_A & recipe_B everyday.

Then I need to sort recipe_A & recipe_B to other two columns (sort_A & sort_B) .

 

so I copy recipe_A & recipe_B to sort_A & sort_B , and run this  script (as below)

In this way , I can recode  sort_A & sort_B.

Current Data Table() << Begin Data Update;
For Each Row( :sort_A = Match( :sort_A, "ZXC", "worse", "QWE", "better", "ASD", "good", :sort_A ) );
Current Data Table() << End Data Update;


Current Data Table() << Begin Data Update;
For Each Row( :sort_B = Match( :sort_B, "R123", "rrr", "H123", "hhh", "G123", "ggg", :sort_B ) );
Current Data Table() << End Data Update;

My question is :

How to create a script to auto copy recipe_A & recipe_B to sort_A & sort_B?

(auto copy if sort_A & sort_B data is missing)?

 

 

example.jmp

txnelson

Below is a rework of your code to check to see if a sort_A or sort_B column exists, and if it does not, create it and populate it.  If it does exist, then repopulate it.  Since the results of sort_A and sort_B are based upon the values of recipe_A and recipe_B, there is not a need to copy the values to sort_A and sort_B, and then changing the values in each.  It is much easier to just use the values in recipe_A and recipe_B to set the values in sort_A and sort_B.

Names Default To Here( 1 );
dt = Current Data Table();

// Get a list of the current columns in the data table
ColList = dt << get column names( string );

// See if a column named "sort_A" does not exist
If( N Rows( Loc( ColList, "sort_A" ) ) == 0,
    // If it does not exist, create the new column and use a formula to populate the values based upon the recipe_A values
	dt << New Column( "sort_A", character, formula( Match( :recipe_A, "ZXC", "worse", "QWE", "better", "ASD", "good", :recipe_A ) ) );
	// Remove the formula which will leave the results
	dt:sort_A << delete formula;
,
	// If the column already exists, then loop through the data table creating new values for sort_A based upon the recipe_A values
	For Each Row( :sort_A = Match( :recipe_A, "ZXC", "worse", "QWE", "better", "ASD", "good", :recipe_A ) )
);

// See if a column named "sort_B" does not exist
If( N Rows( Loc( ColList, "sort_B" ) ) == 0,
	// If it does not exist, create the new column and use a formula to populate the values based upon the recipe_B values
	dt << New Column( "sort_B", character, formula( Match( :recipe_B, "R123", "rrr", "H123", "hhh", "G123", "ggg", :recipe_B ) ) );
	dt:sort_B << delete formula;
,
	// If the column already exists, then loop through the data table creating new values for sort_B based upon the recipe_B values
	For Each Row( :sort_B = Match( :recipe_B, "R123", "rrr", "H123", "hhh", "G123", "ggg", :recipe_B ) )
);