Merging time series with timestamp and duration

Question

I would like to merge the following tables 1 (time series with certain events, ~2000 datapoints) and 2 (time series with duration and amplitude data, ~1,000,000 datapoints) into a table like 3:

1:

timestampevent20:23:56:962A event20:24:14:964B event

......

2:

time [s]

amplitude0-0.0004577640.00006250.0642700195......

3:

timestamptime [s]eventamplitude20:23:56:9620A event-0.00045776420:23:56:9620.0000625 0.064270019520:23:56:9620.000125 0.0927124023...... ...20:24:14:96418.002B event-0.013092041............

I'd be very grateful for your help.

txnelson · Answer

I don't know if this will be helpful, but here is a script that will take your timestamp values(I am assuming the timestamp column as you have it defined is a character column), and convert them into a numeric column.  The new column can then be compared with the time(s) column for putting together the data.

names default to here(1);

// Create the sample data table
dtEvent = New Table( "Events",
	Add Rows( 2 ),
	New Column( "timestamp",
		Character,
		"Nominal",
		Set Values( {"20:23:56:962", "20:24:14:964"} )
	),
	New Column( "event",
		Character,
		"Nominal",
		Set Values( {"A event", "B event"} )
	)
);

// put in a time delay so one can see the initial data table
wait(5);

// Create a numeric version of the timestamp data
dtEvent << New Column( "Numeric timestamp",
	Format(
		"Custom",
		Formula(
			Char( Hour( value ) ) || ":" || Char( Minute( value ) ) || ":" ||
			Char( Floor( Second( value ) ) ) || ":" || Word(
				2,
				Char( Round( Second( value ), 3 ) ),
				"."
			)
		),
		20
	),
	formula(
		Num( Word( 1, :timestamp, ":" ) ) * 3600 + Num( Word( 2, :timestamp, ":" ) ) * 60
		+Num( Word( 3, :timestamp, ":" ) ) + Num( "." || Word( 4, :timestamp, ":" ) )
	)
);

WHG · Answer

My apologies, I didn't make it clear enough:  I would like to synchronize the two tables by associating event A in table 1 with the time duration 0s in table 2.

So, it would be required to simply add the time stamp of event A to the time duration value of each row in table 2, and then associate every following event with the respective "composited" time stamp.

txnelson · Answer

Can you please explain how to determine what rows in Data Table 2 are associated with Event A vs. what rows in Data Table 2 are associated with Even2 B?