topic Re: Select rows that match any value in a list in Discussions

Select rows that match any value in a list

rfeick — Tue, 13 Mar 2018 14:19:02 GMT

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 )));
);

Re: Select rows that match any value in a list

txnelson — Tue, 13 Mar 2018 14:51:42 GMT

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]
		)
	)
);

Re: Select rows that match any value in a list

pmroz — Tue, 13 Mar 2018 14:53:37 GMT

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];
		);
	);
);

Re: Select rows that match any value in a list

rfeick — Tue, 13 Mar 2018 19:16:44 GMT

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

Re: Select rows that match any value in a list

rfeick — Fri, 16 Mar 2018 13:54:00 GMT

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
					);
				);); 
				); 
			);

Re: Select rows that match any value in a list

txnelson — Fri, 16 Mar 2018 14:26:36 GMT

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]
		)
	)
);