Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

   

IDTimeValue
310956601/10/2016 00:0018899
310956601/10/2016 01:0018903
310956601/10/2016 02:0018905
310956601/10/2016 03:0018911
310956601/10/2016 04:0018930
310956601/10/2016 05:0018935

(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:

ID10days_Flag
3109566No
3109567No
3109568Yes

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

  [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.

4 Replies
MVP
MVP

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

  [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.

MVP
MVP

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

Not applicable

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 !!!

Not applicable

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 !