Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
priyarane
Specialist
Specialist

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
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Specialist
Specialist
Author

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
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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;

priyarane
Specialist
Specialist
Author

excellent, working as expected...

priyarane
Specialist
Specialist
Author

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
Partner - Specialist III
Partner - Specialist III

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;