4 Replies Latest reply: Jan 20, 2017 4:37 AM by Antonio Anjos

# Qlik Sense 10+ concesuctive days gap

Hi all,

Let me get straight to it. I am loading the table below and I'm trying to identify when an ID has 10 or more consecutive null values across the 30 days.
The table looks like this

 ID Time Value 3109566 01/10/2016 00:00 18899 3109566 01/10/2016 01:00 18903 3109566 01/10/2016 02:00 18905 3109566 01/10/2016 03:00 18911 3109566 01/10/2016 04:00 18930 3109566 01/10/2016 05:00 18935

(Dont worry this is dummy data)

So on the file attached i have 3 IDs with 24 values per day and 30 days of data. Im looking to get a result like this:

 ID 10days_Flag 3109566 No 3109567 No 3109568 Yes

I have tried all on my knowledge "For next" , "For each next" , "if's" etc.... I am about to say that this is impossible. (Or better done outside Qlik).

So this is my call for help. Is this possible in QlikSense (either on script or set analysis) ?

• ###### Re: Qlik Sense 10+ concesuctive days gap

This is probably the most effective way of getting your results:

```LOAD
ID,
If(Index(Concat(If(IsNull(Value) OR Trim(Value)='',0,1)) ,Repeat('0',10))>0,'Yes','No') AS [10days_flag]
FROM
GROUP BY
ID;
```

The Concat() function creates a string consisting of 0's and 1's denoting null value or not. Then searching
this string for 10 zeros is sufficient to determine if it has at least one consecutive period of 10 nulls.

This requires that the Dev.csv is sorted already on ID and Date as your sample data is.

• ###### Re: Qlik Sense 10+ concesuctive days gap

It works perfectly !!! Plus, it came with a detailed explanation ? hahaha
Amazing !

That was great !
Thank you Petter !!!

• ###### Re: Qlik Sense 10+ concesuctive days gap

The approach with doing it in a chart instead of the load script would be:

1. Create a table chart after having loaded the data table in the load script

2. Select ID as dimension

3. Use exactly the same expression as the one that was in my previous load script approach for measure:

If( Index( Concat( If( IsNull(Value) OR Trim(Value)='' , 0 , 1 ) ) ,Repeat('0',10))>0,'Yes','No')

• ###### Re: Qlik Sense 10+ concesuctive days gap

Because of the size of my data set I'll probably flag that on script for a better performance. But I tested the set analysis and it works nicely as well.

Thank you !