Check for consecutive records over months

[#1]

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;

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD NM_SLTT,

Date(MonthStart(Date#(Month, 'MMM/YYYY')), 'MMM/YYYY') as MonthYear;

LOAD * Inline [

NM_SLTT,Month

Jonh,Jan/2017

Jonh,Feb/2017

Jonh,Mar/2017

Jonh,Apr/2017

Mary,Jan/2017

Mary,Feb/2017

Mary,Apr/2017

Ann,Jan/2017

Ann,Mar/2017

Ann,Apr/2017

];

TempTable:

LOAD NM_SLTT,

MonthYear,

If(NM_SLTT = Previous(NM_SLTT) and MonthYear = MonthStart(Previous(MonthYear), 1), RangeSum(Peek('Flag'), 1), 1) as Flag

Resident Table

Order By NM_SLTT, MonthYear;

Left Join (Table)

LOAD NM_SLTT,

If(Max(Flag) >= 3, 1, 0) as FinalFlag

Resident TempTable

Group By NM_SLTT;

DROP Table TempTable;

[sunny_talwar]

Why is Ann True?

[#4]

Ann is true because her name was in two consecutive months (March and April) and April was the last month we looked. If the last month was May, she would be false.

[#5]

Thanks for the answer! Althoght I am not sure it is quite what I need.

If we have more than three months (which will be the case eventually) this will not work and the count is not reseting when the name doesn't appear on a Month.

Maybe I should do a loop for each month and for each name?

I have modified my load script and am using Sunny solution now.

[sunny_talwar]

So are we looking to check for the most recent two months?

[#7]

Sunny, I've modifield my load script and used your previous solution.

Thanks a lot for the help!