How to match and fill column/rows based on results

cheeseistasty

Hi, I was wondering if this is possible do to write a script for an analysis similar to the one below? In one column ("Month"), I have all 12 months of the year (January to December), with each month having multiple rows in the same column. In the next column ("Condition"), there is a condition for that month, either "A" or "B". In a 3rd column ("Result"), I want to write a formula that analyzes the "Condition" column, where if the value is "A", the "Result" column will have the result of "Good", and if the value is "B", the result will be "Bad". However, even if one row of a certain month has a "B" result, I want all rows of that month to have the result of "Bad". It's a little hard to explain in text, so I have an example below.

 

 

 

jthi

There are many ways to do this especially with scripting.

 

The example below should work (Might be a good idea to split the formula into smaller parts and take a look into Scripting Guide to understand what is going on (especially Loc() function)):

Names Default To Here(1);

dt =  New Table("Untitled",
	Add Rows(20),
	Compress File When Saved(1),
	New Column("Month",
		Character,
		"Nominal",
		Set Values(
			{"Jan", "Jan", "Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Feb", "Feb",
			"Mar", "Mar", "Mar", "Mar", "Mar", "Apr", "Apr", "Apr", "Apr", "Apr"}
		)
	),
	New Column("Condition",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "B", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
			"A", "B", "B", "A", "A", "A"}
		)
	)
);
dt << New Column("Result", Character, Nominal, 
	<< Formula(
		If(Row() == 1,
			monVals = :Month << Get As Matrix;
			condVals = :Condition << Get As Matrix;
		);
		curMonthVals = Loc(monVals, :Month);
		curCond = Loc(condVals[curMonthVals], "B");
		If(N Items(curCond),
			"Bad",
			"Good"
		);
	)
);

I also attached example datatable with the formula included.

example_dt.jmp