topic Re: Create a number for each null period in QlikView

Create a number for each null period

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Create a number for each null period

Brett_Bleess — Tue, 11 Aug 2020 17:43:31 GMT

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/CounterAggregationFunctions/MissingCount.htm

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/1470637

Regards,
Brett

Re: Create a number for each null period

Saravanan_Desingh — Wed, 12 Aug 2020 03:12:31 GMT

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;

Re: Create a number for each null period

Saravanan_Desingh — Wed, 12 Aug 2020 03:13:38 GMT

Output.

Re: Create a number for each null period

aamirSiddiquee1 — Wed, 12 Aug 2020 11:17:09 GMT

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;