Sorting Based - ONLY - value

[priyarane]

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

[Arthur_Fong]

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]

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

[Arthur_Fong · Accepted solution]

Try this:

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;

[priyarane]

excellent, working as expected...

[priyarane]

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 ' ');

[Arthur_Fong · Accepted solution]

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;