Not sure is this is possible...I have a data table that has a column which is either a 1 or 2 (its called channel). In another column there is a designation of a 'test'. It is numeric like 10 or 20 or 30.
What I would like is to create another column that concates all the 'test' for channel 1 and then for channel 2. So each line of data will have a column that has the concat of the test based on channel. So it may look like 10|30|80 for channel 1 but 20|40|30 for channel 2.
The data table could have 1000's lines of result data from different tests. I am interested in seeing what channel uses which test by machine. Since different machines can have different tests on different channels.
I might also need to add another qualifier. So that the 'test' is concate by not only by channel channel but also machine (which denoted by a serial number).
This would be in script for to include into a post-script query.
Any suggestions are appreciated!
ScriptingRe: Not sure is this is possible...Doing what you want will not be very difficult. However, your description is a little bit confusing. Would it be possible for you to provide a small example of the data table, and what you want as this new column you are proposing?Re: Not sure is this is possible...Sure attached is an example table. Only 1 machine is listed but more than 1 could in in the table. Also, over time the testnumber by channel could change.
I can tabulate the data to get which channel has which test number by date but is there a way to indicate that in a column for each line of data. The testinitiationdatelocal could be used as shortdate to get the day.
Re: Not sure is this is possible...Sure attached is an example table. Only 1 machine is listed but more than 1 could in in the table. Also, over time the testnumber by channel could change.
I can tabulate the data to get which channel has which test number by date but is there a way to indicate that in a column for each line of data. The testinitiationdatelocal could be used as shortdate to get the 'date'
Re: Not sure is this is possible...Here is a script that calculates the tests into a single column. Just make your table with the testnumber data the active data table, by clicking on it, and then run this script.
names default to here(1);
// Set up a handle to point to the example data table
dt=current data table();
// Find the testnumbers for each machine/channel combination
dtSumm = dt << summary(
Group( :Machine, :Channel,:testnumber),
N,
Link to original data table(0)
);
// Create a new table to accumulate the testnumbers into a single value
dtFinal = New Table("Concat Test Number",
New Column("Machine", character),
New Column("Channel"),
New Column("Tests", character)
);
For(i=1,i<=N Rows(dtSumm), i++,
If(i==1,
dtFinal << add Rows(1);
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtSumm:Machine[i] != dtSumm:Machine[i-1] |
dtSumm:Channel[i] != dtSumm:Channel[i-1]
,
dtFinal << add Rows(1);
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtFinal:Tests[N Rows(dtFinal)] = dtFinal:Tests[N Rows(dtFinal)] || "/" || char( dtSumm:testnumber[i])
)
);
// Merge the new column back to the origianl table
dt = dt << Update(
With( dtFinal ),
Match Columns( :Machine = :Machine, :channel = :Channel )
);
close( dtSumm, nosave );
close( dtFinal, nosave );
Re: Not sure is this is possible...Wow! amazing! Thank you!
Re: Not sure is this is possible...So how would I include a date element? In the examplev2 there is a column with date. 1 Data point for channel 2 uses Test 40 so this should show up for data from that date (7/4). It should not show up for the other lines for other dates for channel 2 since those dates test 40 was not used.
Re: Not sure is this is possible...All I think that needs to be
names default to here(1);
// Set up a handle to point to the example data table
dt=current data table();
// Find the testnumbers for each machine/channel combination
dtSumm = dt << summary(
Group( :Date, :Machine, :Channel,:testnumber),
N,
Link to original data table(0)
);
// Create a new table to accumulate the testnumbers into a single value
dtFinal = New Table("Concat Test Number",
New Column("Date"),
New Column("Machine", character),
New Column("Channel"),
New Column("Tests", character)
);
For(i=1,i<=N Rows(dtSumm), i++,
If(i==1,
dtFinal << add Rows(1);
dtFinal:Date[N Rows(dtFinal)] = dtSumm:Date[i];
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtSumm:Machine[i] != dtSumm:Machine[i-1] |
dtSumm:Channel[i] != dtSumm:Channel[i-1]
,
dtFinal << add Rows(1);
dtFinal:Date[N Rows(dtFinal)] = dtSumm:Date[i];
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtFinal:Tests[N Rows(dtFinal)] = dtFinal:Tests[N Rows(dtFinal)] || "/" || char( dtSumm:testnumber[i])
)
);
// Merge the new column back to the origianl table
dt = dt << Update(
With( dtFinal ),
Match Columns( :Date = :Date, :Machine = :Machine, :channel = :Channel )
);
close( dtSumm, nosave );
close( dtFinal, nosave );
done, is to add Date in as an additional classifying column, like "Machine", and "Channel".
Re: Not sure is this is possible...I see.
When I applied it the scrip worked. Except I noticed in the data some lines had it missing. I cant quite figure out what is going on with these lines and why it will not work.
Re: Not sure is this is possible...I failed to add the Date[i] vs. Date[i-1]. I added that to line 29, and the script appears to be working.......please check it out
names default to here(1);
// Set up a handle to point to the example data table
dt=current data table();
// Find the testnumbers for each machine/channel combination
dtSumm = dt << summary(
Group( :Date, :Machine, :Channel,:testnumber),
N,
Link to original data table(0)
);
// Create a new table to accumulate the testnumbers into a single value
dtFinal = New Table("Concat Test Number",
New Column("Date"),
New Column("Machine", character),
New Column("Channel"),
New Column("Tests", character)
);
For(i=1,i<=N Rows(dtSumm), i++,
If(i==1,
dtFinal << add Rows(1);
dtFinal:Date[N Rows(dtFinal)] = dtSumm:Date[i];
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtSumm:Date[i] != dtSumm:Date[i-1] | dtSumm:Machine[i] != dtSumm:Machine[i-1] |
dtSumm:Channel[i] != dtSumm:Channel[i-1]
,
dtFinal << add Rows(1);
dtFinal:Date[N Rows(dtFinal)] = dtSumm:Date[i];
dtFinal:Machine[N Rows(dtFinal)] = dtSumm:Machine[i];
dtFinal:Channel[N Rows(dtFinal)] = dtSumm:Channel[i];
dtFinal:Tests[N Rows(dtFinal)] = char( dtSumm:testnumber[i])
,
dtFinal:Tests[N Rows(dtFinal)] = dtFinal:Tests[N Rows(dtFinal)] || "/" || char( dtSumm:testnumber[i])
)
);
// Merge the new column back to the origianl table
dt = dt << Update(
With( dtFinal ),
Match Columns( :Date = :Date, :Machine = :Machine, :channel = :Channel )
);
close( dtSumm, nosave );
close( dtFinal, nosave );Re: Not sure is this is possible...- You need to read the Scripting Guide in the JMP Documentation Library
- Yes, the syntax is different, which is what you will learn by reading the documentation. The JSL you suggest and I modified below will work for a column formula. If this was in open JSL code, the references to sampletype and sampleid would require a subscript pointing to the row in question.
If( :sampletype == "Control" & contains(:sampleid,"Neg"), Col Max( :results))
Re: Not sure is this is possible...I had it right for the contains....but made errors else as it was a more complicated formula...silly me
Re: Not sure is this is possible...I will read the guide more!Re: Not sure is this is possible...Thank you!
Works great!
In an If statement can one have something
If (:sampletype =="Conrol" & :sampleid contains "Neg", Col Max(:results), .)
