Creating Multiple Dependent Columns and assign formula

[#1]

Hi,

I have the following table and I would like to automate result columns based on the formula used.

Serial	Main_rad	SNR_Sub_rad0	SNR_Sub_rad1	SNR_Sub_rad2	SNR_OK_NG	Amp_Sub_rad0	Amp_Sub_rad1	Amp_Sub_rad2	Amp_OK_NG
XWW001	0	25	23	23		5	3	5
XWW001	1	30	27	28		5	3	5
XWW001	2	26	28	27		3	5	2
XWW003	0	23	24	26		2	4	4
XWW003	1	22	25	26		3	3	3
XWW003	2	24	24	25		2	4	5
XWW006	0	30	28	27		4	5	4
XWW006	1	29	29	30		3	4	3
XWW006	2	25	28	26		2	5	5

In this case, my SNR_OK_NG column will take the result from SNR_Sub_rad0 to SNR_Sub_rad2 which means that it require a formula. 

Usually I compute this formula manually -> If( Sum( :SNR_Sub_rad0 > 26,  :SNR_Sub_rad1 > 26, : :SNR_Sub_rad0 > 26 ) >= 2,
1, 0
);

Since I am dealing with more than 10 Pass/Fail condition on weekly basis that involves either based on single column or multiple columns results computation, it will be easier to do it once and for all instead of manually insterting columns and using formula to get the result.

Please suggest a way to do it.

Thanks.

[#2]

I am not sure what you are asking for.  Are you just looking for a script that creates these columns for you?

[#3]

Hi Jim,

Sorry if my previous explanation is confusing. I'm attaching a sample file. 

Usually this is my raw file from database as shown below:

Serial	Main_rad	SNR_Sub_rad0	SNR_Sub_rad1	SNR_Sub_rad2	Amp_Sub_rad0	Amp_Sub_rad1	Amp_Sub_rad2	Noise_Asym
XWW001	0	25	23	23	5	3	5	2001
XWW001	1	30	27	28	5	3	5	1830
XWW001	2	26	28	27	3	5	2	1656
XWW003	0	23	24	26	2	4	4	1246
XWW003	1	22	25	26	3	3	3	2142
XWW003	2	24	24	25	2	4	5	1930
XWW006	0	30	28	27	4	5	4	1673
XWW006	1	29	29	30	3	4	3	1887
XWW006	2	25	28	26	2	5	5	1680

Every time during my analysis, I will have to manually add the _OK_NG columns(with formula) in order to get its status as shown below:

Serial	Main_rad	SNR_Sub_rad0	SNR_Sub_rad1	SNR_Sub_rad2	SNR_OK_NG	Amp_Sub_rad0	Amp_Sub_rad1	Amp_Sub_rad2	Amp_OK_NG	Noise_Asym	Noise_Asym_OK_NG
XWW001	0	25	23	23	OK	5	3	5	NG	2001	NG
XWW001	1	30	27	28	NG	5	3	5	NG	1830	OK
XWW001	2	26	28	27	OK	3	5	2	OK	1656	OK
XWW003	0	23	24	26	OK	2	4	4	OK	1246	OK
XWW003	1	22	25	26	OK	3	3	3	OK	2142	NG
XWW003	2	24	24	25	OK	2	4	5	OK	1930	OK
XWW006	0	30	28	27	NG	4	5	4	OK	1673	OK
XWW006	1	29	29	30	NG	3	4	3	OK	1887	OK
XWW006	2	25	28	26	OK	2	5	5	NG	1680	OK

Now I intended to get a script to automate - by adding _OK_NG_columns with its status. For the Noise_Asym status, I can get a script for it but those Sub_rad, I can't find a way out.

Thanks.

[#4 · Accepted solution]

Hi Guys,

Sorry, I think I have found an easy way to do it.

dt = Current Data Table();
New column ("SNR_OK_NG",
formula (If( Sum( :SNR_Sub_rad0 > 26, :SNR_Sub_rad1 > 26, :SNR_Sub_rad2 > 26 ) >= 2,
	"NG",
	"OK"
		)
	)
);
New column ("AMP_OK_NG",
formula (If( Sum( :Amp_Sub_rad0 > 4, :Amp_Sub_rad1 > 4, :Amp_Sub_rad2 > 4 ) >= 2,
	"NG",
	"OK"
		)
	)
);
New column ("Noise_Asyn_OK_NG",
formula (If( :Noise_Asym > 2000,
	"NG",
	"OK"
		)
	)
);

So it's good now.. :)

Welcome any suggestion/method as well.

Thanks.

[#5]

Can you set up the data table that you want manually to make sure that it is what you want and all the formulas work as expected?

If you can, then the next question is, "Is that table enough?" Can it be re-used as a template with new data but the same columns and formulas? This simple approach satisfies many situations.

If not, then use the completed data table as a template for your script. (Jim has started that approach here.)