Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nikita42
New Contributor

Need to count number of consecutive weeks with the same status

Hello,

I have a table like this:

DateTarget IDStatus
May-01A1Red
Apr-24A1Red
Apr-17A1

Red

Apr-10A1Red
Apr-3A1Green
Mar-27A1Red

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.

Tags (1)
8 Replies
antoniotiman
Honored Contributor III

Re: Need to count number of consecutive weeks with the same status

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
New Contributor

Re: Need to count number of consecutive weeks with the same status

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
Honored Contributor III

Re: Need to count number of consecutive weeks with the same status

Try to Replace

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

MVP
MVP

Re: Need to count number of consecutive weeks with the same status

1.jpg

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
New Contributor

Re: Need to count number of consecutive weeks with the same status

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
Honored Contributor III

Re: Need to count number of consecutive weeks with the same status

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

nikita42
New Contributor

Re: Need to count number of consecutive weeks with the same status

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
Honored Contributor III

Re: Need to count number of consecutive weeks with the same status

Maybe

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