nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Assigning values to new column based on 3 exsisting columns that are a result of a binning formula

TimeTortoise

Hi JMP Community,

I am trying to find a clean way to assign a value to a column based on values of 3 columns which have been created using JMP's binning formula. Specifically, my team has found that 3 raw material metrics influence how our process runs best (the first one grouped into 4 categories & the other two grouped into 2 categories, giving a total of 16 categories). We would like to mine our historical process data and assign a Bin it. Any suggestions on what tool might be able to help do this?

Attached is a snip showing how our bin's are structured.

Best -TimeTortise

Snip of Bin Layout.jpg

Accepted answers

txnelson

I didn't test this formula for your final bin column, but it should work

As Constant(
	combos = {"HighHighHigh", "HighHigBaseline", "HighBaselineHigh", "HighBaselineBaseline", 
	"ElevatedHighHigh", "ElevatedHigBaseline", "ElevatedBaselineHigh", "ElevatedBaselineBaseline", 
	"BaselineHighHigh", "BaselineHigBaseline", "BaselineBaselineHigh", "BaselineBaselineBaseline", 
	"UnknownHighHigh", "UnknownHigBaseline", "UnknownBaselineHigh", "UnknownBaselineBaseline"};
	binList = "ABCDEFGHIJKLMNOP";
);

"Bin " || Substr( binList, Contains( combos, :Moisture || :Mw || :LOI ), 1 );

All comments

jthi

For which column are you looking the values for? And what do you consider "clean"? If it is a Bin column you could: concatenate all the three columns together to create a new column. Then map that to bin values using Match() in a formula or by using Value Codes column property. You could also just create mapping table and use Join/Update to add the binning values

txnelson

I didn't test this formula for your final bin column, but it should work

As Constant(
	combos = {"HighHighHigh", "HighHigBaseline", "HighBaselineHigh", "HighBaselineBaseline", 
	"ElevatedHighHigh", "ElevatedHigBaseline", "ElevatedBaselineHigh", "ElevatedBaselineBaseline", 
	"BaselineHighHigh", "BaselineHigBaseline", "BaselineBaselineHigh", "BaselineBaselineBaseline", 
	"UnknownHighHigh", "UnknownHigBaseline", "UnknownBaselineHigh", "UnknownBaselineBaseline"};
	binList = "ABCDEFGHIJKLMNOP";
);

"Bin " || Substr( binList, Contains( combos, :Moisture || :Mw || :LOI ), 1 );

TimeTortoise

Jim,

Thanks that is a very clever use of those two functions! I will need to rewrite for my table but it is more than enough to get me on the right path. I would never have thought of such an elegant solution.

Best -Ben