Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

priyarane
Valued Contributor

Sorting Based - ONLY - value

Hi Community,

I have a data like below from there I need values only if Seq is 1,2,3,4,5, not others. In the below example this seq is 2 times only .

 

SeqValueOriginalSeq
1101
2152
3203
4254
5305
5356
6407
7458
1509
25510
36011
46512
17013
27514
38015
48516
59017
Labels (2)
2 Solutions

Accepted Solutions
Partner
Partner

Re: Sorting Based - ONLY - value

Try this:

MC.PNG

Script:

Raw:
load * inline [
Seq Value OriginalSeq
1 10 1
2 15 2
3 20 3
4 25 4
5 30 5
5 35 6
6 40 7
7 45 8
1 50 9
2 55 10
3 60 11
4 65 12
1 70 13
2 75 14
3 80 15
4 85 16
5 90 17
](delimiter is ' ');

Data:
load *,//if(RowNo()=1,Seq,IF(PEEK(NewSeq)<>5,peek(NewSeq)+1,1))AS NewSeq,
if(RowNo()=1,Seq,
if(Seq=1,1,IF(Seq-PEEK(NewSeq2)=1,peek(NewSeq2)+1,0)))
AS NewSeq2 resident Raw
order by OriginalSeq;

Data2:
load *,if(peek(NewSeq2)=1 and NewSeq2<5,0,if(peek(NewSeq3)=0 and NewSeq2<>5 ,0,NewSeq2))
//ewSeq2
as NewSeq3
resident Data
order by OriginalSeq desc;

NoConcatenate
Data3:
load * resident Data2
where NewSeq3<>0;

drop table Data2;
drop table Data;
DROP TABLE Raw;

exit Script;

View solution in original post

Partner
Partner

Re: Sorting Based - ONLY - value

Replace Data2 script with this:

Data2:
load *,if(RowNo()=1 and NewSeq2<>5,0, if(peek(NewSeq2)=1 and NewSeq2<5,0,if(peek(NewSeq3)=0 and NewSeq2<>5 ,0,NewSeq2)))
//ewSeq2
as NewSeq3
resident Data
order by OriginalSeq desc;

View solution in original post

6 Replies
Partner
Partner

Re: Sorting Based - ONLY - value

What do you mean by this seq is 2 times only?

Maybe you can share how is the expected output looks like so that we can have a clear picture. 

priyarane
Valued Contributor

Re: Sorting Based - ONLY - value

so only highlighted values only qualified in the below example.

Requirement: sequence has to complete for: 1 to 5 in order others are not consider..

12345 - it is sequence so qualified, 543 and 1234 and 435 - these are not qualified as sequence is not completed(12345).

 

SeqValueOriginalSeq
1101
2152
3203
4254
5305
5356
6407
7458
1509
25510
36011
46512
17013
27514
38015
48516
59017
Partner
Partner

Re: Sorting Based - ONLY - value

Try this:

MC.PNG

Script:

Raw:
load * inline [
Seq Value OriginalSeq
1 10 1
2 15 2
3 20 3
4 25 4
5 30 5
5 35 6
6 40 7
7 45 8
1 50 9
2 55 10
3 60 11
4 65 12
1 70 13
2 75 14
3 80 15
4 85 16
5 90 17
](delimiter is ' ');

Data:
load *,//if(RowNo()=1,Seq,IF(PEEK(NewSeq)<>5,peek(NewSeq)+1,1))AS NewSeq,
if(RowNo()=1,Seq,
if(Seq=1,1,IF(Seq-PEEK(NewSeq2)=1,peek(NewSeq2)+1,0)))
AS NewSeq2 resident Raw
order by OriginalSeq;

Data2:
load *,if(peek(NewSeq2)=1 and NewSeq2<5,0,if(peek(NewSeq3)=0 and NewSeq2<>5 ,0,NewSeq2))
//ewSeq2
as NewSeq3
resident Data
order by OriginalSeq desc;

NoConcatenate
Data3:
load * resident Data2
where NewSeq3<>0;

drop table Data2;
drop table Data;
DROP TABLE Raw;

exit Script;

View solution in original post

priyarane
Valued Contributor

Re: Sorting Based - ONLY - value

excellent, working as expected...

priyarane
Valued Contributor

Re: Sorting Based - ONLY - value

Just now found small bug in this code,

for ex if add 1 at the end of the data then 1 also repeating.

Raw:
load * inline [
Seq Value OriginalSeq
1 10 1
2 15 2
3 20 3
4 25 4
5 30 5
5 35 6
6 40 7
7 45 8
1 50 9
2 55 10
3 60 11
4 65 12
1 70 13
2 75 14
3 80 15
4 85 16
5 90 17

1 100 18

](delimiter is ' ');

Partner
Partner

Re: Sorting Based - ONLY - value

Replace Data2 script with this:

Data2:
load *,if(RowNo()=1 and NewSeq2<>5,0, if(peek(NewSeq2)=1 and NewSeq2<5,0,if(peek(NewSeq3)=0 and NewSeq2<>5 ,0,NewSeq2)))
//ewSeq2
as NewSeq3
resident Data
order by OriginalSeq desc;

View solution in original post