Condition to exclude data

[#1]

Hello,

From the below table am trying to achieve the sequence only which has 1,2,3,4 for the test field.

Having a challenge to create the condition to exclude which doesn't have all the 4 Sequences in the script.

It would be great if someone can help on how to restrict test field values which doesn't have all the 4 seq as mean a test field value should have all the 4 seq (i.e, 1,2,3,4 should be present if not restrict).

test	seq
116425	2
129561	2
129561	3
139697	2
139697	3
161410	2
174989	2
220020	1
220020	2
221035	2
229783	2
232599	1
237923	2
237923	3
237952	2
238887	2
10200010	4
10202181	1
10202181	2
10202181	4
10202445	3
10202181	1
10202181	2
10202181	3
10202181	4

Thanks in advance.

[santiago_respane]

Hi,

if you want just to exclude all rows that have 4 as value in seq field you can do like this:

exclude directly in where condition

MyTable:

LOAD test,

          seq

WHERE seq <> 4;

Hope this helps.

Kind regards,

[chrismarlow]

Not sure I fully follow as it would seem none of your sample meets what I think is your criteria (I am reading as 'test' exists for all of seq=1, 2, 3 and 4, to then include).

Does the following help?

data:
load * Inline [
test ,seq
116425, 2 
129561, 2 
129561, 3 
139697, 2 
139697, 3 
161410, 2 
174989, 2 
220020, 1 
220020, 2 
221035, 2 
229783, 2 
232599, 1 
237923, 2 
237923, 3 
237952, 2 
238887, 2 
10200010, 4 
10202181, 1 
10202181, 2 
10202181, 4 
10202445, 3 ];

distinct_test:
NoConcatenate
Load Distinct
test
Resident data;

left join (distinct_test)
load Distinct
test,
'Y' AS seq_1
Resident data
where seq=1;

left join (distinct_test)
load Distinct
test,
'Y' AS seq_2
Resident data
where seq=2;

left join (distinct_test)
load Distinct
test,
'Y' AS seq_3
Resident data
where seq=3;

left join (distinct_test)
load Distinct
test,
'Y' AS seq_4
Resident data
where seq=4;

drop table data;

distinct_test_final:
NoConcatenate
load
*,
if(seq_1='Y' AND seq_2='Y' and seq_3='Y' and seq_4='Y','YES','NO') AS Include
Resident distinct_test;

drop Table distinct_test;

[sunny_talwar]

May be like this:

Table:

LOAD test,

     seq

FROM

[https://community.qlik.com/thread/218365]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD test

Where Count = 1;

LOAD test,

  If(Count(DISTINCT seq) = 4, 1, 0) as Count

Resident Table

Group By test;

[#5]

Hello Santiago,

No, i would like to achieve the test field to show only for those which has all the 1,2,3,4 seq available only.

All others should be excluded.

Thanks.

[Kushal_Chawda]

But suppose distinct sequence is 2,3,4,5 then your logic will be wrong. am I correct? He needs only 1,2,3,4 seq

[sunny_talwar]

Le bhai:

Table:

LOAD test,

     seq

FROM

[https://community.qlik.com/thread/218365]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD test

Where Count = 1;

LOAD test,

  If(Concat(DISTINCT seq) = 1234, 1, 0) as Count

Resident Table

Group By test;

[Kushal_Chawda]

Ha abhi barabar na bhai

[Kushal_Chawda]

one more way

Data:
LOAD * Inline [
test,    seq
116425,    2
129561,    2
129561,    3
139697,    2
139697,    3
161410,    2
174989,    2
220020,    1
220020,    2
221035, 2
229783,    2
232599,    1
237923,    2
237923,    3
237952,    2
238887,    2
10200010,  4
10202181,  1
10202181,  2
10202181,  4
10202445,  3
10202181,  1
10202181,  2
10202181,  3
10202181,  4 ] ;

New:
LOAD *
where Sum =10;
LOAD test as Test,
Sum(DISTINCT seq) as Sum
Resident Data

WHERE seq >=1 and seq <=4
Group by test;

Final:
NoConcatenate
LOAD Distinct *
Resident Data
where Exists(Test,test);

DROP Tables Data,New;

!

[sunny_talwar]

Aab agar seq hui 5,4,1,0 toh? Sum(Distinct seq) = 10