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 for Programming
 LabTalk Forum
 put entire row in a new worksheet after find max		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

olsy

29 Posts
Posted - 08/30/2011 :  08:14:51 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Origin Ver. and Service Release (Select Help-->About Origin): OriginPro 8.5
Operating System:Win7

Hi!
i am trying to find a max of a column of all worksheets and put then whole row consisting this value to a new worksheet. But till now it doesn´t work...i am a new in programming so please help me with it..it´s already second day i spend on it..


//test to make sure OriginPro is installed
if (system.product&1 != 1)
{
type "This feature is only available in OriginPro.";
break;
}

//create a new book and import data file


dlgfile g:=Excel; // Open file dialog
impAsc; // Import selected file


//store the values of the desired column in a range

range BCol = [%(page.name$)]%(wks.name$)!col(B);
range sourceWks = [%(page.name$)]%(wks.name$)!;


//create a tree to hold the result of frequency count and do frequency
//count to "Make" column to find the discrete "Parameter1" designations
//this X-Function is only available in OriginPro

tree tr;
discfreqs irng:=BCol rd:=tr;

//create a string array and put the "Parameter1" designations into it

StringArray sa;
sa.append(tr.FreqCount1.Data1);

//if sa contains data
//loop over each unique "Parameter1" designation
//and extract data from original worksheet to new workbook

if (sa.GetSize() != NANUM)
{
for (ii=1; ii<=sa.GetSize(); ii++)
{
//this string is used to generate condition to
//point to different "Parameter1" designation stored
//in string array for each iteration
string newWk$ = sa.GetAt(ii)$;

//construct tested condition for wxt
string cond$ = "BCol$==" + newWk$;

//wxt extracts rows of the original sheet according to
//the currently tested state and put them to a new
//worksheet named by the corresponding designation
wxt test:=cond$ iw:=sourceWks ow:=[<input>]<new %(newWk$)>;
}


}

//get max efficiency

for (jj=2; jj<=page.nLayers-1; jj++) //loop over all layers (worksheets)
{

page.active = jj; //make the worksheet active
jj=;
page.active =;
%a=page.active;
double dd=max(col(I));
dd=;
wxt test:="(col(I)==dd)" ow:="The best"; //extract rows with maximum PCE to a new worksheet
}

it put only the last value in the new worksheet

DataConv

Germany
60 Posts
Posted - 08/30/2011 :  09:15:37 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Did i get that right: You have a Workbook with several sheets in it. Now you want to have the maximum value of one specific column in such a sheet collected and put them in another workbook...

My guess: using a loop over all sheets and store in the target...
win -a IMPORTBOOK; // activate workbook
int i01; // for debuging and memory management
dataset d01; // temporary storage
loop (i01,1,page.nlayers) { // looping all sheets
 page.active=i01; // activate sheet by sheet
 if (i01==1) { // dataset issue:
  d01={max(wcol(SOURCE))}; // initialize dataset
 } else {
  d01[i01]=max(wcol(SOURCE)); // append value
 };
win -a TARGETBOOK; // activate output sheet
wcol(TARGETCOLUMN)=d01; // write dataset to column

Note 1: replace all italic entries with your names.
Note 2: this routine works with index to the column rather than its name. If your 'B' column is the second one of the sheet, replace SOURCE by the value '2'...
Go to Top of Page

DataConv

Germany
60 Posts
Posted - 08/30/2011 :  09:25:24 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Ok - now i got your idea: You want to copy the column with the max value to a new sheet...
Instead of the part:
win -a TARGETBOOK; // activate output sheet
wcol(TARGETCOLUMN)=d01; // write dataset to column

use
dataset d02; // second temporary dataset
vfind ix:=d01 v:=max(d01) ox:=d02; // gives index to the column with the maximum value
i01=d02[1]; // get only first one
range r01=[IMPORTBOOK]$(i01)!SOURCE;
range r02=[TARGETBOOK]INDEXTARGETSHEET!INDEXTARGETCOLUMN;
r02=r01; // copy column
Edited by - DataConv on 08/30/2011 09:26:22 AM
Go to Top of Page

olsy

29 Posts
Posted - 08/30/2011 :  09:39:47 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
thanks, but how i can put entire row with this value to a new worksheet?
If i have a table:
Samplename Par1 Par2 Jsc Voc PCE


i am looking for the same value of Par1 and put them in different worksheets. So i have wks1, wks2 and ....
i need to find the max of PCE from each of this created worksheets and put whole row (Samplename Par1 Par2 Jsc Voc PCE) with this max value from each worksheet to a new worksheet with a name Best...
Go to Top of Page

DataConv

Germany
60 Posts
Posted - 08/31/2011 :  01:19:21 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
I would do following:
* looping each imported sheet
** (re)define range to the PCE of the actual sheet
** find maximum value of PCE (1st post)
** using vfind to determine the index of the row in the column/sheet containing the maximum (2nd post)
** using wrcopy (X-Function - have a look at it! Very powerful command!) to copy row to a new sheet with the loop variable as index for the target row...

Is this you were looking for?
Edited by - DataConv on 08/31/2011 01:19:50 AM
Go to Top of Page

olsy

29 Posts
Posted - 08/31/2011 :  03:40:02 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
thank you for idea )
but now i did this:

loop (i01,1,page.nlayers)
{ // looping all sheets//loop over all layers (worksheets)
page.active=i01; // activate sheet by sheet
page.active =;
range ICol = [%(page.name$)]%(page.active$)!col(I);
if (i01==1)
{ // dataset issue:
d01={max(wcol(9))}; // initialize dataset
} else
{
d01[i01]=max(wcol(9)); // append value
};
dataset d02; // second temporary dataset
vfind ix:=d01 v:=max(d01) ox:=d02; // gives index to the column with the maximum value
i01=d02[1]; // get only first one
wrcopy iw:=[%(page.name$)]$(i01)! dr1:=$(i01) ow:=[Book2]1;


and got error: #user abort

have any idea what is wrong?
Go to Top of Page

DataConv

Germany
60 Posts
Posted - 08/31/2011 :  06:32:32 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Maybe:
Do a 
del -ra icol;
right before your range definition inside your loop. Redefining of a range variable is IMHO not (yet?) possible. Therefore clear/free first the variable, then define it.

To my opinion, your script does not what you stated. As far as I understood, you want to have the row with the max PCE for each sheet collected together in a new sheet...
Then you need to wrap the loop all around the script and you can neglect the dataset version i proposed earlier. You should also consider to set more of the wrcopy parameters like r1, r2 (if you want to copy just one row, r2 and r1 need to be assigned with the same value to avoid erratic behaviour). See: http://wiki.originlab.com/~originla/wiki2/index.php?title=X-Function:Wrcopy
Hint: When inserting a script here, use the 'Code'-Button (the one with the '#'). Better to read...
Edited by - DataConv on 08/31/2011 06:33:56 AM
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