Create a number for each null period

[sergio0592]

Hi all,

I'am struggling with the following case. I have DT field, DT_EVENT field. What i'am trying to achieve is to create a number in my load statement for each period with no DT_EVENT.

Input:

Expected output :

Afterthat, i will be able to count the longest period with no Event (5 in the exemple above).

Thanks for your help.

[Brett_Bleess]

Found how you can get a count of things, but I think that may give you some ideas on how to go about things, as once they are grouped, you may be able to search on those rows missing and use RowNo() or RecNo() to maybe number them...  My post will kick things back out, so someone else may provide some better ideas too.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Count...

The following Design Blog post may be of some help too:

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534

One other one:

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

Regards,
Brett

[Saravanan_Desingh]

One solution is.

tab1:
LOAD *, If(IsNull(DT_EVENT) Or DT_EVENT='',Key) As Number;
LOAD *, If(Peek(DT_EVENT)<>'' And DT_EVENT='', RangeSum(Peek(Key),1), Peek(Key)) As Key;
LOAD * INLINE [
    DT, DT_EVENT
    1/1/2020, 
    1/2/2020, 1/2/2020
    1/3/2020, 
    1/4/2020, 1/4/2020
    1/5/2020, 1/5/2020
    1/6/2020, 
    1/7/2020, 1/7/2020
    1/8/2020, 
    1/9/2020, 
    1/10/2020, 
    1/11/2020, 
    1/12/2020, 
    1/13/2020, 1/13/2020
    1/14/2020, 1/14/2020
];

Drop Field Key;

[Saravanan_Desingh]

Output.

[aamirSiddiquee1]

Resident the Main table again , and then put a if Condition,

Load DT , DT_EVENT , if(DT_EVENT<>'',rowno() ) as NewNumberColumn 

Resident Your Previous loaded table;