# New to QlikView

Discussion board where members can get started with QlikView.

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 .

 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
Labels (2)

• ### Script

2 Solutions

Accepted Solutions
Partner

## Re: Sorting Based - ONLY - value

Try this:

Script:

Raw:
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:
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:
where NewSeq3<>0;

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

exit Script;

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;

6 Replies
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.

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).

 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
Partner

## Re: Sorting Based - ONLY - value

Try this:

Script:

Raw:
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:
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:
where NewSeq3<>0;

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

exit Script;

Valued Contributor

## Re: Sorting Based - ONLY - value

excellent, working as expected...

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:
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

## 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;