Select a timeseries of only the records that changed by ticker in

Community Support
1

I want to query a table and get only the rows that changed over time (for a particualr id). 

Example below:

q)tab: (;ticker:AAPL AMZN AAPL MSFT MSFT AAPL AMZN AMZN AAPL MSFT MSFT;

time:10:00:00.100 10:15:34.980 11:45:50.345 11:00:55.500 11:31:46.410 13:37:58.125 10:35:25.540 11:17:00.600 14:09:20.750 12:18:28.830 13:40:13.000;

flag:GreenYellowGreenRedGreenYellowRedGreenYellowGreen`Green;

score:24 14 24 8 27 17 9 21 16 27 21)

q)tab

tickertimeflagscore

AAPL10:00:00.100Green24

AMZN10:15:34.980Yellow14

AAPL11:45:50.345Green24

MSFT11:00:55.500Red8

MSFT11:31:46.410Green27

AAPL13:37:58.125Yellow17

AMZN10:35:25.540Red9

AMZN11:17:00.600Green21

AAPL14:09:20.750Yellow16

MSFT12:18:28.830Green27

MSFT13:40:13.000Green21

Expected Output:

tickertimeflagscore

AAPL10:00:00.100Green24

AAPL13:37:58.125Yellow17

AAPL14:09:20.750Yellow16

AMZN10:15:34.980Yellow14

AMZN10:35:25.540Red9

AMZN11:17:00.600Green21

MSFT11:00:55.500Red8

MSFT11:31:46.410Green27

MSFT13:40:13.000Green21

In above example, the for ticker AAPL, first two rows over time (the ones at 10:00 & 11:45 o’clock) didn’t change (flag and score remained same), therefore, only the first row shows up in output. 

The values for ‘score’ changed for AAPL in the next two rows of AAPL, therefore those are included in the output.

2

q)select from tab where (differ;flip(flag;score)) fby ticker
ticker time flag score
--------------------------------
AAPL 10:00:00.100 Green 24
AMZN 10:15:34.980 Yellow 14
MSFT 11:00:55.500 Red 8
MSFT 11:31:46.410 Green 27
AAPL 13:37:58.125 Yellow 17
AMZN 10:35:25.540 Red 9
AMZN 11:17:00.600 Green 21
AAPL 14:09:20.750 Yellow 16
MSFT 13:40:13.000 Green 21

3

you can sort the table, then only select the rows that ‘differ’ (excluding the time column):

q)tab where differ delete time from tab:tickertime xasc tab
ticker time         flag   score

AAPL   10:00:00.100 Green  24  
AAPL   13:37:58.125 Yellow 17  
AAPL   14:09:20.750 Yellow 16  
AMZN   10:15:34.980 Yellow 14  
AMZN   10:35:25.540 Red    9    
AMZN   11:17:00.600 Green  21  
MSFT   11:00:55.500 Red    8    
MSFT   11:31:46.410 Green  27  
MSFT   13:40:13.000 Green  21  

4

Another method that avoids sorting is applying the differ function on flag/score fby ticker:

q)select from tab where (differ;(flag;score)) fby ticker
ticker time flag score
--------------------------------
AAPL 10:00:00.100 Green 24
AMZN 10:15:34.980 Yellow 14
MSFT 11:00:55.500 Red 8
MSFT 11:31:46.410 Green 27
AAPL 13:37:58.125 Yellow 17
AMZN 10:35:25.540 Red 9
AMZN 11:17:00.600 Green 21
AAPL 14:09:20.750 Yellow 16
MSFT 13:40:13.000 Green 21  

5

Thanks Nick! It worked for me!

6

Thanks Ajay! It is a little variation from Nick’s solution. I eventually used this one.

7

Thanks! almost same as suggested by Ajay, works perfect.

8

q)select from tab where(differ;(flag;score))fby ticker
ticker time         flag   score

AAPL   10:00:00.100 Green  24
AMZN   10:15:34.980 Yellow 14
MSFT   11:00:55.500 Red    8
MSFT   11:31:46.410 Green  27
AAPL   13:37:58.125 Yellow 17
AMZN   10:35:25.540 Red    9
AMZN   11:17:00.600 Green  21
AAPL   14:09:20.750 Yellow 16
MSFT   13:40:13.000 Green  21

q)select[<(ticker;time)]from tab where(differ;(flag;score))fby ticker
ticker time         flag   score

AAPL   10:00:00.100 Green  24
AAPL   13:37:58.125 Yellow 17
AAPL   14:09:20.750 Yellow 16
AMZN   10:15:34.980 Yellow 14
AMZN   10:35:25.540 Red    9
AMZN   11:17:00.600 Green  21
MSFT   11:00:55.500 Red    8
MSFT   11:31:46.410 Green  27
MSFT   13:40:13.000 Green  21