Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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
Partner

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
Partner

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
Partner

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
Partner

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

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