Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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.
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 |
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;
excellent, working as expected...
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 ' ');
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;