How to find the number of consecutive records which has zero or unknown values

[Programmer77]

I have a data set like this 

Columns:

Id | date | value 

Value contains 0 and unknown. I would like to find the records having consecutive zero or unknown 

I have tried the below script:

If(value=0 and previous(value)=0 , missing 

Ordered by date

But it does not work as i need to flag more than 7 consecutive 0 or unknown based on order of date

Can anyone suggest any solution for this

[MayilVahanan]

HI Try like below

Load *, if(id = Peek(id) and ((value = 0 or value = 'unknown') and (peek(value)=0 or Peek(value)='unknown')), peek('missing')+1, 1) as missing Inline
[
id, date, value
1, 12/01/2022, 10
1, 13/01/2022, 0
1, 14/01/2022, 0
1, 15/01/2022, unknown
1, 16/01/2022,0
1, 17/01/2022, 0
1, 18/01/2022, 0
1, 19/01/2022, 0
1, 20/01/2022, 14
2, 12/01/2022, 11
2, 13/01/2022, 0
2, 14/01/2022, 0
];

[vinieme12]

A slightly simpler logic 🙂

if(id = Peek(id) ,if(WildMatch(value,0,'Unknown'),peek('missing')+1),0) as missing

Also you need to order by both ID,Date

[Programmer77]

Hi .Thank you very much for the help.  I wanted to check if I can just flag for the ones which has the 0 or unknown values . My final output should have the dates range and the number of missing values.

Output example:

Id | Daterange|number of missing values

1 | 25april-29apri| 4

[vinieme12]

Full script;

modified the data to test multiple daterange of missing values in same ID

Also assuming your raw table is already sorted by ID and Date Ascending

raw:
Load *
,if(WildMatch(value,0,'Unknown'),if(id=peek(id),alt(peek('missing'),0)+1,1),0) as missing
,if(WildMatch(value,0,'Unknown'),if(id=peek(id),if(peek('startdate')=0,date,peek('startdate'))),0) as startdate
Inline
[
id, date, value
1, 12/01/2022, 10
1, 13/01/2022, 0
1, 14/01/2022, 0
1, 15/01/2022, unknown
1, 16/01/2022,10
1, 17/01/2022, 0
1, 18/01/2022, 0
1, 19/01/2022, 0
1, 20/01/2022, 14
2, 12/01/2022, 11
2, 13/01/2022, 0
2, 14/01/2022, 0
];

NoConcatenate
temp:
load
id
,startdate&'-'&date as Daterange
,missing as missingcount
resident raw
Where Previous(startdate)=0
order by id,date desc
;
drop table raw;

exit script;

output

[vinieme12 · Accepted solution]

@Programmer77 please close the thread by accepting a response as solution