Detect consecutive dates ranges using q?

Community Support
1

Hi,

I am new to q. I am trying to find continuous time range for table below? Maybe it’s easy but I can’t figure it out on Friday afternoon…

q)show test:( c1:aaabbaaabb`a; c2: 2016.01.01+til 11)

c1 c2                                                            

-------------                                                    

a  2016.01.01                                                    

a  2016.01.02                                                    

a  2016.01.03                                                    

b  2016.01.04                                                    

b  2016.01.05                                                    

a  2016.01.06                                                    

a  2016.01.07                                                    

a  2016.01.08                                                    

b  2016.01.09                                                    

b  2016.01.10                                                    

a  2016.01.11             

how do I query it so that I can get:

c1 StartDate     EndDate

a   2016.01.01  2016.01.03

b   2016.01.04  2016.01.05

a   2016.01.06  2016.01.08

b   2016.01.09  2016.01.10

a   2016.01.11  2016.01.11                                     

Many thanks!

Shaw

2

Use differ, and reverse to get the endings:

q)test:( c1:aaabbaaabb`a; c2: 2016.01.01+til 11)

q)select c1, start:c2, end:(exec reverse c2 from reverse test where differ c1) from test where differ c1

c1 start      end       

a  2016.01.01 2016.01.03

b  2016.01.04 2016.01.05

a  2016.01.06 2016.01.08

b  2016.01.09 2016.01.10

a  2016.01.11 2016.01.11

3

Couple of ways to do this:

  1. Using groupby

    q) value select first c1,startDate:first c2, endDate:last c2 by sums differ c1 from test

  1. Take the row indexes where symbol value changes and filter out values from columns using those indexes.

    q)  r: where differ test`c1
    q)  select c1 r, startDate:c2 r, endDate: c2 1_(r,count test)-1 from test

Second solution performs better than the first.

4

I see, very helpful. Thank you all! 

I added extra conditions to make sure c2 is consecutive in days…

works if there is gap between days. 

test:( c1:aaabbaaabbaa; c2:(2016.01.01+til 11), 2016.02.01)

r: asc distinct ((where differ (1i, 1_deltas testc2)), (where differ testc1))

select c1 r, startDate:c2 r, endDate: c2 1_(r,count test)-1 from test 

r startDate  endDate

a 2016.01.01 2016.01.03

b 2016.01.04 2016.01.05

a 2016.01.06 2016.01.08

b 2016.01.09 2016.01.10

a 2016.01.11 2016.01.11

a 2016.02.01 2016.02.01

Have a great weekend!

 

5

An alternative that might be more intuitive:

q)test:([] c1:aaabbaaabbaa; c2:(2016.01.01+til 11), 2016.02.01)q)select first c1,start:first c2,end:last c2 by sums 1<>(deltas@;c2)fby c1 from testc1| c1 start end--| ------------------------1 | a 2016.01.01 2016.01.032 | b 2016.01.04 2016.01.053 | a 2016.01.06 2016.01.084 | b 2016.01.09 2016.01.105 | a 2016.01.11 2016.01.116 | a 2016.02.01 2016.02.01