How do I create a new data table combining duplicate rows?

gmc5149 — Thu, 18 Apr 2024 16:22:29 GMT

I have a indexed list of production runs of different products (A-H) and their start and end times. Sometimes there are two lines in a row with the same product (rows 1 & 2 and 7 & 8) and I would like those to be combined into one row with the start time of the first row and end time of the second row. How would I use JSL to do this?

Re: How do I create a new data table combining duplicate rows?

jthi — Thu, 18 Apr 2024 16:39:06 GMT

I think this should do what you want.

Add new (numeric) column to your table to calculate "groups" with formula like

If(Row() == 1,
	counter = 0;
);
If(:Product != Lag(:Product),
	counter++;
);
counter;

Then you can use Summary table to perform the min/max part

Remove and re-order columns from summary table as needed

Here is a script from Enhanced Log to perform those actions

// Open Data Table: Run Time Index.jmp
// → Data Table("Run Time Index")
Open("$DOWNLOADS/Run Time Index.jmp");

// New column: R
Data Table("Run Time Index") << New Column("R",
	Numeric,
	"Continuous",
	Format("Best", 12),
	Formula(
		If(Row() == 1, counter = 0);
		If(:Product != Lag(:Product),
			counter
			++);
		counter;
	)
);

// Data table summary
// → Data Table("Run Time Index By (R, Product)")
Data Table("Run Time Index") << Summary(
	Group(:R, :Product),
	Min(:Run Index),
	Min(:Start),
	Max(:End),
	Freq("None"),
	Weight("None"),
	statistics column name format("column"),
	Link to original data table(0)
);

// Delete columns
Data Table("Run Time Index By (R, Product)") << Delete Columns(:R, :N Rows);

// Move selected column: Run Index
Data Table("Run Time Index By (R, Product)") << Move Selected Columns({:Run Index}, To First);

// Change column modeling type: Run Index
Data Table("Run Time Index By (R, Product)"):Run Index << Set Modeling Type("Ordinal");

Re: How do I create a new data table combining duplicate rows?

txnelson — Thu, 18 Apr 2024 17:07:30 GMT

Re: How do I create a new data table combining duplicate rows?

gmc5149 — Thu, 18 Apr 2024 17:17:27 GMT

thank you!

topic Re: How do I create a new data table combining duplicate rows? in Discussions

How do I create a new data table combining duplicate rows?

Re: How do I create a new data table combining duplicate rows?

Re: How do I create a new data table combining duplicate rows?

Re: How do I create a new data table combining duplicate rows?