Hi all
I have a table which is aggregated by hour, like this
| time | sym | x | y |
| 10:00 | a | 2 | 4 |
| 11:00 | a | 2 | 3 |
| 12:00 | a | 1 | 3 |
| 13:00 | a | 1 | 3 |
| 14:00 | a | 1 | 3 |
| 15:00 | a | 1 | 2 |
| 10:00 | b | 2 | 4 |
| 11:00 | b | 2 | 3 |
| 12:00 | b | 1 | 3 |
| 13:00 | b | 1 | 3 |
| 14:00 | b | 1 | 3 |
| 15:00 | b | 1 | 2 |
I need to join this with other table with 5 min timebin, which maybe start at 10:05 / 10:10, thus I would like to replicate the data from 10:00 to 10:05, … 10:55 to join it, any suggestion of how to do this?
Are you looking for something along these lines?
ungroup select time+\:00:05*til 12, sym, x, y from t1
Why can you not use asof join?
Thanks, yes, that what I need, thanks a lot
For asof join, I thought we usually use that to join trade and quote, which to get the most recent quote for trade, where in this joining, we will remove the quote data which cannot match, so it is quite difference, isn’t it?
Maybe I have misinterpreted your question but I thought you wanted to duplicate rows in a table from your original example to be able to join it with another one where timestamps are more granular. For example:
time sym z p / table2----------------------10:05:00.000 a 20 4010:10:00.000 a 20 3010:15:00.000 a 10 3010:20:00.000 a 10 3010:25:00.000 a 10 3010:30:00.000 a 10 2010:35:00.000 b 20 4010:40:00.000 b 20 3010:45:00.000 b 10 3010:50:00.000 b 10 3010:55:00.000 b 10 30
Then aj[symtime;table2;table1] is
time sym z p x y--------------------------10:05:00.000 a 20 40 2 410:10:00.000 a 20 30 2 410:15:00.000 a 10 30 2 410:20:00.000 a 10 30 2 410:25:00.000 a 10 30 2 410:30:00.000 a 10 20 2 410:35:00.000 b 20 40 2 410:40:00.000 b 20 30 2 410:45:00.000 b 10 30 2 410:50:00.000 b 10 30 2 410:55:00.000 b 10 30 2 4
I.e. values of z and p from table1 are used with prevailing values of x and y from table1.