cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar

Fill in column values based on a sequence creation relying on table values

I have a process data table including Start timestamp and End timestamp. I would like that the duration of the process is calculated from the timestamps, and then additional rows are added .Column values of start timestamp are added by increasing the time by 1 minute till the End time is reached. All the other values in the table should be just copied to the next row.

IloinenHamsteri_0-1726224711439.png

IloinenHamsteri_0-1726224911360.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
jthi
Super User

Re: Fill in column values based on a sequence creation relying on table values

How does your starting data look like (I assume the image is from end result)?

 

Edit: Other image loaded a bit slower

-Jarmo

View solution in original post

jthi
Super User

Re: Fill in column values based on a sequence creation relying on table values

Here is one option which might work. It does create a new table instead of adding rows to the existing one

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(2),
	Compress File When Saved(1),
	New Column("S", Character, "Nominal", Set Values({"A1", "A2"})),
	New Column("Start",
		Numeric,
		"Continuous",
		Format("d/m/y h:m:s", 22, 0),
		Input Format("d/m/y h:m:s", 0),
		Set Values([3661768800, 3661769400])
	),
	New Column("End",
		Numeric,
		"Continuous",
		Format("d/m/y h:m:s", 22, 0),
		Input Format("d/m/y h:m:s", 0),
		Set Values([3661768980, 3661769700])
	),
	New Column("Data", Character, "Nominal", Set Values({"A", "B"}))
);

dt_new = dt << Clone;
dt_new << Delete Rows(1::NRows(dt_new));

For Each Row(dt,
	For(i = 0, i <= Date Difference(:Start, :End, "minute"), i++,
		dt_new << Add Rows(1);
		dt_new[N Rows(dt_new), 0] = dt[Row(), 0];
		dt_new[N Rows(dt_new), "Start"] = Date Increment(:Start, "minute", i);
	);
);

Start

jthi_0-1726227346090.png

End

jthi_2-1726227422041.png

 

 

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

Re: Fill in column values based on a sequence creation relying on table values

How does your starting data look like (I assume the image is from end result)?

 

Edit: Other image loaded a bit slower

-Jarmo
jthi
Super User

Re: Fill in column values based on a sequence creation relying on table values

Here is one option which might work. It does create a new table instead of adding rows to the existing one

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(2),
	Compress File When Saved(1),
	New Column("S", Character, "Nominal", Set Values({"A1", "A2"})),
	New Column("Start",
		Numeric,
		"Continuous",
		Format("d/m/y h:m:s", 22, 0),
		Input Format("d/m/y h:m:s", 0),
		Set Values([3661768800, 3661769400])
	),
	New Column("End",
		Numeric,
		"Continuous",
		Format("d/m/y h:m:s", 22, 0),
		Input Format("d/m/y h:m:s", 0),
		Set Values([3661768980, 3661769700])
	),
	New Column("Data", Character, "Nominal", Set Values({"A", "B"}))
);

dt_new = dt << Clone;
dt_new << Delete Rows(1::NRows(dt_new));

For Each Row(dt,
	For(i = 0, i <= Date Difference(:Start, :End, "minute"), i++,
		dt_new << Add Rows(1);
		dt_new[N Rows(dt_new), 0] = dt[Row(), 0];
		dt_new[N Rows(dt_new), "Start"] = Date Increment(:Start, "minute", i);
	);
);

Start

jthi_0-1726227346090.png

End

jthi_2-1726227422041.png

 

 

-Jarmo

Re: Fill in column values based on a sequence creation relying on table values

@jthi I just tested it with the real data and it works very nicely. Thanks, this is exactly what I wanted to have.