topic Re: Qlik Sense 10+ concesuctive days gap in App Development

Qlik Sense 10+ concesuctive days gap

Thu, 19 Jan 2017 11:16:06 GMT

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) ?

thanks for your time guys

Re: Qlik Sense 10+ concesuctive days gap

petter — Thu, 19 Jan 2017 14:44:35 GMT

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 
  [LIB://Downloads/Dev.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
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

petter — Thu, 19 Jan 2017 14:48:56 GMT

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

Fri, 20 Jan 2017 09:37:25 GMT

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

Fri, 20 Jan 2017 10:01:04 GMT

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 !