Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nikita42
Partner
Partner

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.

8 Replies
antoniotiman
Master III
Master III

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
Partner
Partner
Author

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
Master III
Master III

Try to Replace

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

maxgro
MVP
MVP

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
Partner
Partner
Author

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
Master III
Master III

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

nikita42
Partner
Partner
Author

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
Master III
Master III

Maybe

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