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

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

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

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

 

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.