453462_en-US

liliconcom

update table based on timestamp

Hi,

I have two tables. (A and

Table A contain (2000events along the duration of a test) events marking the beginning of a period and the name of the period at specific timestamps (underlined line is header)

TimeStamp, Period

30-12-2021 17:35:49.1, A 

30-12-2021 17:39:36.8, B 

...

so Period A is between "30-12-2021 17:35:49.1" and "30-12-2021 17:39:36.8"

 

Table B is of a log file recording data also with timestamps (underlined line is header) around 6000000 datapoints

timestamp, sensor1, sensor2, sensor3,... 

30-12-2021 17:37:21.2, value, value, value,...

30-12-2021 17:37:22.2, value, value, value,...

30-12-2021 17:37:23.2, value, value, value,...

 

I would like to add a now column to table B with the Period value from Table A  based on the value of the timestamp in B.

If timestamp in B is bigger or equal than timestamp in Row (N) in table A AND also smaller than timestamp in Row (N+1) of table A then the period value in Row (N) will be assigned for that record in table B (timestamp in B is between two timestamps in A)

 

Any suggestions?

 

Thanks

 

 

WindowsRe: update table based on timestamp

Here is a possible solution using @ErraticAttach  suggestion on the method to use.

  Names Default To Here( 1 );
dta = Data Table( "table A" );
dtb = Data Table( "table B" );

// Place all timestamp values from Table A & B 
timesa = dta:timestamp << get values;
timesb = dtb:timestamp << get values;

// Find the row to add the data to
rows = Loc Sorted( timesa, timesb );

// Add the new columns to Table A
dta << New Column( "sensor1" );
dta << New Column( "sensor2" );
dta << New Column( "sensor3" );

// Loop through matrix rows and add the records to the appropriate
// row in Table A

For( i = 1, i <= N Rows( rows ), i++,
 theRow = rows[i];
 dta:sensor1[theRow] = dtb:sensor1[i];
 dta:sensor2[theRow] = dtb:sensor2[i];
 dta:sensor3[theRow] = dtb:sensor3[i];
);

 

Re: update table based on timestamp

Sort the lookup table and use Loc Sorted(). Check the function in the Scripting Index to see how it works.