topic Re: Check for consecutive records over months in QlikView

Check for consecutive records over months

Fri, 25 Aug 2017 11:25:44 GMT

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,

0),0) as FL_PC_CONSEC

Resident TB_2

;

DROP Table TB_1;

Re: Check for consecutive records over months

sunny_talwar — Fri, 25 Aug 2017 13:47:27 GMT

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;

Re: Check for consecutive records over months

sunny_talwar — Fri, 25 Aug 2017 13:49:47 GMT

Why is Ann True?

Re: Check for consecutive records over months

Fri, 25 Aug 2017 14:17:04 GMT

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.

Re: Check for consecutive records over months

Fri, 25 Aug 2017 14:35:49 GMT

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.

Re: Check for consecutive records over months

sunny_talwar — Fri, 25 Aug 2017 15:38:29 GMT

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

Re: Check for consecutive records over months

Fri, 25 Aug 2017 18:45:43 GMT

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

Thanks a lot for the help!