Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Indicate sequence between specified rows

Hi,

I'm evaluating a log file. In the file there are logs continuously. Some of the lines are related. There is a line that says that an event started, then there are logs about the event, and then the event ends. Then there are unrelated lines until (the same event) starts again with loglines until the end-event. 

Basically there are several colums in a CSV file, one of them is the description of the event which can be lots of thing, one is StartEvent another is StopEvent. 

By goal is to have only the events between the start and stop row, and have indicator added to each row that it is related to the first of the eventsequence, the second, third etc. 

my data looks like this

Descr, comment, value
whatever1, aComment, 12
whatever2, anotherComment,88
StartEvent,,
something1,comment,334
something2,comment,444
Stopevent,,
whatever3,acomment,83
whatever1,acomment,0
whatever4,acoment,89
Startevent,,
Something2,comment,9939
.
.
Stopevent

ect.

I like to get

Descr, comment, value,eventseq
whatever1, aComment, 12,0
whatever2, anotherComment,88,0
StartEvent,,,1
something1,comment,334,1
something2,comment,444,1
Stopevent,,,1
whatever3,acomment,83,0
whatever1,acomment,0,0
whatever4,acoment,89,0
Startevent,,,2
Something2,comment,9939,2
.,2
.,2
Stopevent,,,2

Any hints ?

Thanks,

Carl

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

One solution is:

tab1:
LOAD *, If(Descr='StartEvent',K1,If(Previous(Descr)='StopEvent' Or IsNull(Peek('K1')) Or Peek('K2')=0,0,K1)) As K2;
LOAD *
	 ,If(Descr='StartEvent',RangeSum(Peek('K1'),1), Peek('K1')) As K1
	 ;
LOAD RecNo() As RowID, * INLINE [
    Descr, comment, value
    whatever1, aComment, 12
    whatever2, anotherComment, 88
    StartEvent,,
    something1,comment,334
    something2,comment,444
    StopEvent,,
    whatever3,acomment,83
    whatever1,acomment,0
    whatever4,acoment,89
    StartEvent,,
    Something2,comment,9939
    .
    .
    StopEvent
    Something3
];
Drop Field K1;

commQV13.PNG

View solution in original post

1 Reply
Highlighted
Specialist III
Specialist III

One solution is:

tab1:
LOAD *, If(Descr='StartEvent',K1,If(Previous(Descr)='StopEvent' Or IsNull(Peek('K1')) Or Peek('K2')=0,0,K1)) As K2;
LOAD *
	 ,If(Descr='StartEvent',RangeSum(Peek('K1'),1), Peek('K1')) As K1
	 ;
LOAD RecNo() As RowID, * INLINE [
    Descr, comment, value
    whatever1, aComment, 12
    whatever2, anotherComment, 88
    StartEvent,,
    something1,comment,334
    something2,comment,444
    StopEvent,,
    whatever3,acomment,83
    whatever1,acomment,0
    whatever4,acoment,89
    StartEvent,,
    Something2,comment,9939
    .
    .
    StopEvent
    Something3
];
Drop Field K1;

commQV13.PNG

View solution in original post