topic update table based on timestamp in Discussions

update table based on timestamp

Shahars — Mon, 24 Jan 2022 13:35:29 GMT

Hi,

I have two tables. (A and B)

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

Re: update table based on timestamp

ErraticAttack — Mon, 24 Jan 2022 14:28:25 GMT

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

Re: update table based on timestamp

txnelson — Mon, 24 Jan 2022 15:37:33 GMT

Here is a possible solution using @ErraticAttack 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

Shahars — Tue, 25 Jan 2022 11:37:33 GMT

Thank you very much ErraticAttach and txnelson

Jim, could it be that the indexing is backwards somehow?

the Loc sorted is getting for each row in B the row number in Table A that contain the information I want to put in table B

However, i get an error inside the Loop about the scoping.

1. if I understand correctly, "rows" should have the same number of rows as table B

2. the parameter "i" increase from 1 to the number of rows in B.

3. in each iteration of the loop , the parameter "theRow" is assigned the corresponding i'th number in "rows".
so it actually holds the a number representing a specific line in table A that holds the data I want.

4. so now all we need to do is read the value in the appropriate column in A on that line and put it in the i'th row of B in the column I want.

However, I get an error about "scoped data table access requires a data table column or variable in access or evaluation of ..."

looking at the code I think that the script is referencing the wrong number as the index.

so I Switched between [i] and [theRow]

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

should actually read

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

however that does not help either.

I am still not familiar with JSL, so could it be that the way I specify the values or the column and row be incorrect?

Thank you, very much.

Re: update table based on timestamp

Shahars — Tue, 25 Jan 2022 12:08:27 GMT

solved this by changing the loop to be

For( i = 1, i <= N Rows( rows ), i++,
	theRow = rows[i];
	//show(theRow);	
	//show(subscript(dta,theRow,15));
	subscript(dtb,i,8) = subscript(dta,theRow,17);
	subscript(dtb,i,9) = subscript(dta,theRow,15);
	subscript(dtb,i,10) = subscript(dta,theRow,16);
);

however if there is a quicker way to run it I would be happy to learn. Thanks.