Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
UberBock
Level III

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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 );

 

Jim

View solution in original post

13 REPLIES 13
Highlighted
txnelson
Super User

Re: 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?
Jim
Highlighted
UberBock
Level III

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.

Highlighted
UberBock
Level III

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'

Highlighted
txnelson
Super User

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 );

 

Jim

View solution in original post

Highlighted
UberBock
Level III

Re: Not sure is this is possible...

Wow!  amazing!  Thank you!

Highlighted
UberBock
Level III

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.

Highlighted
txnelson
Super User

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".

Jim
Highlighted
UberBock
Level III

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.

Highlighted
txnelson
Super User

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 );
Jim
Article Labels