Need to count number of consecutive weeks with the same status

[nikita42]

Hello,

I have a table like this:

Date	Target ID	Status
May-01	A1	Red
Apr-24	A1	Red
Apr-17	A1	Red
Apr-10	A1	Red
Apr-3	A1	Green
Mar-27	A1	Red

I need to flag all targets that have been red for 3 or more weeks and also count the number of consecutive weeks that it has stayed red. So, basically I need to count the number of rows where Status=Red starting from the top and stop counting once I reach the fifth row where the status has changed. Any prior incidents where the status was red should be ignored.

Every week, I'll get a new row of data with an updated status so the date/row numbers will need to be dynamic.

[antoniotiman]

Hi Nikita,

Temp:
LOAD *,
Date(Date#(Date&'-'&Year(Today()),'MMM-DD-YYYY')) as Date1;
LOAD * inline [
Date 'Target ID' Status
May-01 A1 Red
Apr-24 A1 Red
Apr-17 A1 Red
Apr-10 A1 Red
Apr-3 A1 Green
Mar-27 A1 Red
May-01 A11 Red
Apr-24 A11 Red
Apr-17 A11 Green
Apr-10 A11 Red
Apr-3 A11 Green
Mar-27 A1 Red
] (delimiter is spaces);

NoConcatenate LOAD *,
If([Target ID]=Peek([Target ID]) and Status=Peek(Status) and Status='Red',Autonumber(RowNo(),[Target ID]),1) as Flag
Resident Temp
Order By [Target ID],Date1;
Drop Table Temp;

Regards,

Antonio

[nikita42]

Hi Antonio,

Your solution comes close, but it's got some glitches.

I have a case like this when I use your script:

Target     Date          Status     Flag

A1          Apr-01          Red     1

A1          Apr-08          Red     1

A1          Apr-15          Red     2

A1          Apr-22          Green   1

A1          Apr-29          Red     1

A1          May-06         Red     3

So, the first 2 rows both get counted as 1 and in some cases, the count does not restart correctly. Unfortunately I can't attach a sample qvw, but these 2 issues are found in several targets.

[antoniotiman]

Try to Replace

If([Target ID]=Peek([Target ID]) and Status=Peek(Status) and Status='Red',Peek(Flag)+1,1) as Flag

[maxgro]

Temp:

LOAD *,

Date(Date#(Date&'-'&Year(Today()),'MMM-DD-YYYY')) as Date1;

LOAD * inline [

Date 'Target ID' Status

May-01 A1 Red

Apr-24 A1 Red

Apr-17 A1 Red

Apr-10 A1 Red

Apr-3 A1 Green

Mar-27 A1 Red

May-01 A11 Red

Apr-24 A11 Red

Apr-17 A11 Green

Apr-10 A11 Red

Apr-3 A11 Green

Mar-27 A1 Red

Apr-01 C1          Red  

Apr-08    C1      Red    

Apr-15      C1    Red    

Apr-22        C1  Green  

Apr-29 C1          Red   

May-06  C1       Red    

] (delimiter is spaces);

Temp1:

NoConcatenate load Distinct *

resident Temp;

Left join (Temp)

load

  [Target ID], Status, Date1,

  Date,

  If([Target ID]=Peek([Target ID]) and Status=Peek(Status) and Status='Red', Peek('Flag1')+1, 1) as Flag1

Resident Temp1

Order By [Target ID], Date1;

DROP Table Temp1;

Final:

NoConcatenate LOAD

  *,

  if(Peek('FlagGE3')=1 and [Target ID]=Peek([Target ID]) and Status='Red' or (Flag1 >= 3 and Status='Red'), 1, 0) as FlagGE3

Resident Temp

Order By [Target ID], Date1 desc;

DROP Table Temp;

[nikita42]

Yes! This works perfectly.

One problem though, the requirement has now changed to count both Red and Amber statuses in this way, so can I change the statement to if Status= Red or Status=Amber?

Will the peek(Status) bit still work?

[antoniotiman]

Like this ? Or I don't understant your new requirement.

[nikita42]

Basically, imagine a table like:

Target     Date          Status     Flag

A1          Apr-01          Red     1

A1          Apr-08          Amber 2

A1          Apr-15          Red     3

A1          Apr-22          Green   1

A1          Apr-29          Red     1

A1          May-06         Red     2

So the flag should +1 if the status is Red OR amber, but not green. So, I can't use Status=Peek(Status) here.

[antoniotiman]

Maybe

If([Target ID]=Peek([Target ID]) and (Peek(Status)='Red' or Peek(Status)='Amber') and Status <> 'Green',Peek(Flag)+1,1) as Flag