Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

Create a number for each null period

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:

P1.png

Expected output :

P2.png

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

Thanks for your help.

4 Replies
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Specialist III
Specialist III

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;
Highlighted
Specialist III
Specialist III

Output.

commQV64.PNG

Highlighted
Contributor
Contributor

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;