cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
rfeick
Level IV

Select rows that match any value in a list

I was having an issue with a more complicated script and wrote a simple test script to try and fix the issue, but can't figure out what's wrong. It's probably glaringly obvious, but I've been looking at it for too long to notice. I create a table with :Disposition Type, :NC Code, and :NC Qty. The original script takes user input to create the list of codes, but here I predefined the list. I want to create a column for each code listed in the codes list that copies over the value in :NC Qty if :NC Code matches the code from the list. It recognizes codes[c] to name the column, but not in the column formula. I've also tried using Contains instead of == in the formula, but that didn't seem to make a difference.

 New Table( "NC Qty Col Test",
	Add Rows( 5 ),
	New Column( "Disposition Type",
		Character,
		"Nominal",
		Set Values(
			{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
			"DEVIATE-PRODUCT"}
		)
	),
	New Column( "NC Code",
		Character,
		"Nominal",
		Set Values( {"007", "014", "020", "007", "008"} )
	),
	New Column( "NC Qty",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5] )
	)
);

codes = {"007", "014"};

For(c=1, c<=NItems(codes), c++,
	New Column("Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula(If(:NC Code==codes[c], :NC Qty, 0 )));
);
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Select rows that match any value in a list

The issue is that your value of "c" changes, and is not static within the formula definition.  Therefore, you need to force the code to have a static value of "codes[c]" in the formula.  Below is one way to do that:

New Table( "NC Qty Col Test",
	Add Rows( 5 ),
	New Column( "Disposition Type",
		Character,
		"Nominal",
		Set Values( {"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS", "DEVIATE-PRODUCT"} )
	),
	New Column( "NC Code", Character, "Nominal", Set Values( {"007", "014", "020", "007", "008"} ) ),
	New Column( "NC Qty", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) )
);

codes = {"007", "014"};

For( c = 1, c <= N Items( codes ), c++,
	Eval(
		Substitute(
				Expr(
					New Column( "Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula( If( :NC Code == __code__, :NC Qty, 0 ) ) )
				),
			Expr( __code__ ), codes[c]
		)
	)
);
Jim

View solution in original post

pmroz
Super User

Re: Select rows that match any value in a list

This one is a little tricky.  When you use variables in a formula you have to initialize them inside the formula.  But I did it without using formulas.

dt = New Table( "NC Qty Col Test",  Add Rows( 5 ),
	New Column( "Disposition Type", Character, "Nominal",
		Set Values(
			{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
			"DEVIATE-PRODUCT"}
		)
	),
	New Column( "NC Code", Character, "Nominal",
		Set Values( {"007", "014", "020", "007", "008"} )
	),
	New Column( "NC Qty", Numeric, "Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5] )
	)
);
codes = {"007", "014"};
For(c = 1, c <= NItems(codes), c++,
	dt << New Column("Code " || codes[c] || " NC Qty", numeric, "Continuous")
);
for (k = 1, k <= nitems(codes), k++,
	r = dt << get rows where(:NC Code == codes[k]);
	colname = "Code " || codes[k] || " NC Qty";
	if (nrows(r) > 0,
		for (i = 1, i <= nrows(r), i++,
			one_row = r[i];
			column(dt, colname)[one_row] = column(dt, "NC Qty")[one_row];
		);
	);
);

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Select rows that match any value in a list

The issue is that your value of "c" changes, and is not static within the formula definition.  Therefore, you need to force the code to have a static value of "codes[c]" in the formula.  Below is one way to do that:

New Table( "NC Qty Col Test",
	Add Rows( 5 ),
	New Column( "Disposition Type",
		Character,
		"Nominal",
		Set Values( {"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS", "DEVIATE-PRODUCT"} )
	),
	New Column( "NC Code", Character, "Nominal", Set Values( {"007", "014", "020", "007", "008"} ) ),
	New Column( "NC Qty", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) )
);

codes = {"007", "014"};

For( c = 1, c <= N Items( codes ), c++,
	Eval(
		Substitute(
				Expr(
					New Column( "Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula( If( :NC Code == __code__, :NC Qty, 0 ) ) )
				),
			Expr( __code__ ), codes[c]
		)
	)
);
Jim
pmroz
Super User

Re: Select rows that match any value in a list

This one is a little tricky.  When you use variables in a formula you have to initialize them inside the formula.  But I did it without using formulas.

dt = New Table( "NC Qty Col Test",  Add Rows( 5 ),
	New Column( "Disposition Type", Character, "Nominal",
		Set Values(
			{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
			"DEVIATE-PRODUCT"}
		)
	),
	New Column( "NC Code", Character, "Nominal",
		Set Values( {"007", "014", "020", "007", "008"} )
	),
	New Column( "NC Qty", Numeric, "Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5] )
	)
);
codes = {"007", "014"};
For(c = 1, c <= NItems(codes), c++,
	dt << New Column("Code " || codes[c] || " NC Qty", numeric, "Continuous")
);
for (k = 1, k <= nitems(codes), k++,
	r = dt << get rows where(:NC Code == codes[k]);
	colname = "Code " || codes[k] || " NC Qty";
	if (nrows(r) > 0,
		for (i = 1, i <= nrows(r), i++,
			one_row = r[i];
			column(dt, colname)[one_row] = column(dt, "NC Qty")[one_row];
		);
	);
);
rfeick
Level IV

Re: Select rows that match any value in a list

Thanks so much for helping me figure it out! Either of those options would be great to fix my script!

rfeick
Level IV

Re: Select rows that match any value in a list

I was able to use the proposed solution to fix my original question and it seemed to make sense. However, then I realized that I had another portion of the same large script that does a very similar thing to create columns based on a variable value from a list. What I don't understand is why one works and the other doesn't. Here's the other section of script. In this portion I have a cost associated with the NC Code that is only supposed to be applied for Disposition Types of SCRAP-R and SCRAP-DNR. This cost is listed in the Total Cost column.

 

For(c=1, c<=NItems(codes), c++,
				CF<<New Column("Code " || codes[c] || " Cost", Numeric, Continuous, Format(Currency), Formula(If(And(:NC Code == char(codes[c]), :Disposition Type == "SCRAP-R"), :NC Qty with Zeros * :Total Cost,
					And(:NC Code == char(codes[c]), :Disposition Type == "SCRAP-DNR"), :NC Qty with Zeros * :Total Cost,
					And(:NC Code == char(codes[c]), :Disposition Type == "REWORK"), 0,
					And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-MATERIAL"), 0,
					And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-PROCESS"), 0,
					And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-PRODUCT"), 0,
					0
					);
				);); 
				); 
			);
txnelson
Super User

Re: Select rows that match any value in a list

The issue is the same as it was in the first case.  codes[c] will not be interpreted in the formua. You have to substitute the actual value for codes[c] into the formula.

For( c = 1, c <= N Items( codes ), c++,
	Eval(
		Substitute(
				Expr(
					CF << New Column( "Code " || codes[c] || " Cost",
						Numeric,
						Continuous,
						Format( Currency ),
						Formula(
							If(
								And( :NC Code == __code__, :Disposition Type == "SCRAP-R" ),
									:NC Qty with Zeros * :Total Cost,
								And( :NC Code == __code__, :Disposition Type == "SCRAP-DNR" ),
									:NC Qty with Zeros * :Total Cost,
								And( :NC Code == __code__, :Disposition Type == "REWORK" ), 0,
								And( :NC Code == __code__, :Disposition Type == "DEVIATE-MATERIAL" ), 0,
								And( :NC Code == __code__, :Disposition Type == "DEVIATE-PROCESS" ), 0,
								And( :NC Code == __code__, :Disposition Type == "DEVIATE-PRODUCT" ), 0,
								0
							)
						)
					)
				),
			Expr( __code__ ), codes[c]
		)
	)
);
Jim