Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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;
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;
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.
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
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;
Ha abhi barabar na bhai
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;
!
Aab agar seq hui 5,4,1,0 toh? Sum(Distinct seq) = 10