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.
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
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