6 Replies Latest reply: Aug 25, 2017 2:45 PM by Diogo Furlan RSS

    Check for consecutive records over months

    Diogo Furlan

      Hello!

       

      I am trying to check if a name appears in consecutive months and not having a tiny bit of success.

      If the name appears in more than 2 consecutive months, I would like a true value, if not false. And this must reset every time the name doesn't appear on a month.

      So let's say I have 3 people:

       

      NM_SLTT,Month

      Jonh,Jan/2017

      Jonh,Fev/2017

      Jonh,Mar/2017

      Jonh,Apr/2017

      Mary,Jan/2017

      Mary,Fev/2017

      Mary,Apr/2017

      Ann,Jan/2017

      Ann,Mar/2017

      Ann,Apr/2017

       

      This way, John and Ann would be true and Mary false.

       

      I've tried quite a few scritps. The last one is below.

      It's only working for the first NM_SLTT. All the others return 0.

      Can someone give me a hand, please?

       

      Thanks a lot!

       

      TB_1:

      LOAD

       

      NM_SLTT,

      MonthNameField,

      Count(DISTINCT NM_SLTT) as NR_PC_MES

       

      Resident SPEND_VM

      Where DS_CTG_SPEND = 'Condition1'

      Group by NM_SLTT, MonthNameField

      Order by MonthNameField, NM_SLTT DESC

      ;

       

       

      TB_2:

      LOAD

       

      NM_SLTT,

      MonthNameField,

      If(NM_SLTT = Peek(NM_SLTT,-1,'TB_1'),

           If(NR_PC_MES = 1 and Peek(NR_PC_MES,-1,'TB_1') = 1,

                1,

                0),0) as FL_PC_CONSEC

       

      Resident TB_2

      ;

       

      DROP Table TB_1;