The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
 Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum
 Origin Forum
 Mask entire column with intercolumn condition		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

belebele

4 Posts
Posted - 10/28/2021 :  09:01:18 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Hello,

I have five consecutive columns filled with values (columns A to E). From this, I extract the mean values and the standard deviations for each row using the X-function sum(A:E)_mean in col(F) and sum(A:E)_sd in col(G) (see below).



My problem is the following:

I would like to mask entirely one (or more) of the initial columns (A to E) if it contains, between the rows 5 and 15 only, at least one value which is too far from the calculated mean (for example the outlier can be ">(col(F)+col(G))" (mean+sd) and "<(col(F)-col(G))" (mean-sd).

With the command window, this works with the following code:

for(int ii=5; ii<=15; ii++)
{
	double dLower = col(F)[ii]-col(G)[ii];  // mean-sd
	double dUpper = col(F)[ii]+col(G)[ii];  // mean+sd
	for(int jj=1; jj<=5; jj++)
	{
		double dOutlier = col($(jj))[ii];  // get cell value
		if(dOutlier<dLower || dOutlier>dUpper)
		{
			wcellmask irng:=col($(jj));  // mask the cell
		}
	}
};




However, I don't want to use the command window but I want to implement this code as a X-function in all 5 columns. The goal behind this is to run automatically the code every time I import new data (I use a data connector) without any human intervention. Is there a way to do such a thing?

It looks like it is possible to mask cells with a condition intracolumn but not intercolumn.

Thanks for your help.

Best regards,
Greg

YimingChen

1609 Posts
Posted - 10/28/2021 :  10:14:27 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Can you select from menu Worksheet->Worksheet Script.. and put your LabTalk script in the panel. Check the box After Import. Click OK to save. Then each time you connect to a new data file, the script gets executed and the columns should masked according to your condition. See attached workbook as an example. You can click on the Data Connector icon and set the Data Source to check the result.
https://my.originlab.com/ftp/forum_and_kbase/Images/TemplateMask.zip

James
Go to Top of Page

belebele

4 Posts
Posted - 10/28/2021 :  11:15:52 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Thanks James for the help, it works when I click on "Run" (in the "script" tab of the "worksheet properties" window) but not when I import data, even though I've checked "Run After import".

Actually, the problem is that I import my data in 5 different workbooks corresponding to one sample each (named "sample#" with # the number of the sample). Then I gather some of the results in another "summary" workbook.

The columns that I want to mask as described in my post are in the workbook "summary", such that I never import data directly into the worksheet where I have the script.

Greg
Go to Top of Page

YimingChen

1609 Posts
Posted - 10/28/2021 :  12:12:22 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
OK. Assume you already have 5 workbooks with calculated row mean/std in the project. You can run the script below to extract the outlier columns. So in the first loop, the script find the outlier columns in each sheet and label the longname as "outlier". Then in the second loop, the columns with longname "outlier" is then copied to the summary workbook. 


doc -e LB {
	for(int ii=5; ii<=15; ii++)
	{
		double dLower = col(F)[ii]-col(G)[ii];  // mean-sd
		double dUpper = col(F)[ii]+col(G)[ii];  // mean+sd
		for(int jj=1; jj<=5; jj++)
		{
			double dOutlier = col($(jj))[ii];  // get cell value
			if(dOutlier<dLower || dOutlier>dUpper)
			{
				col($(jj))[L]$ = "outlier";
			}
		}
	}	
}

newbook name:="Summary";
int colindex = 1;
doc -e LB {
	for (int j = 1; j <= wks.ncols; j++) {
		if (wcol(j)[L]$ == "outlier") {
			range r1 = wcol(j);
			range r2 = ["Summary"]1!wcol(colindex);
			r2 = r1;
			r2[L]$ = %H;
			colindex++;
		}
	}
}



James
Edited by - YimingChen on 10/28/2021 12:13:13 PM
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000