topic Re: Condition to exclude data in QlikView

Condition to exclude data

Wed, 25 May 2016 17:58:11 GMT

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.

Re: Condition to exclude data

santiago_respane — Wed, 25 May 2016 18:11:15 GMT

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,

Re: Condition to exclude data

chrismarlow — Wed, 25 May 2016 18:12:31 GMT

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;

Re: Condition to exclude data

sunny_talwar — Wed, 25 May 2016 18:13:11 GMT

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;

Re: Condition to exclude data

Wed, 25 May 2016 18:14:27 GMT

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.

Re: Condition to exclude data

Kushal_Chawda — Wed, 25 May 2016 18:31:58 GMT

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

Re: Condition to exclude data

sunny_talwar — Wed, 25 May 2016 18:34:13 GMT

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;

Re: Condition to exclude data

Kushal_Chawda — Wed, 25 May 2016 18:42:23 GMT

Ha abhi barabar na bhai

Re: Condition to exclude data

Kushal_Chawda — Wed, 25 May 2016 18:55:21 GMT

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;

Re: Condition to exclude data

sunny_talwar — Wed, 25 May 2016 20:23:07 GMT

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

Re: Condition to exclude data

MarcoWedel — Wed, 25 May 2016 23:45:41 GMT

Hi,

one front end solution could be a listbox using this expression:

=Aggr(Concat(DISTINCT seq,','),test)

hope this helps

regards

Marco

Re: Condition to exclude data

Kushal_Chawda — Thu, 26 May 2016 03:55:04 GMT

Toh ek where daal denge.. where seq<=4

Re: Condition to exclude data

Thu, 26 May 2016 07:25:07 GMT

see attached qvw

Re: Condition to exclude data

Thu, 26 May 2016 20:05:17 GMT

Sorry for the delay in response.

Sunny- the solutions works accurate Thanks, now the challenge is that user wanting to change the seq range dynamically on UI.

For instance if a user would like to Seq for only 1,2,3,4,5 they would like to have this as on the fly.

Any thoughts on this.

Re: Condition to exclude data

sunny_talwar — Thu, 26 May 2016 20:09:27 GMT

You mean that all the data will already be in the application (for all test) and we would need to show only those test where all the seq exists for the user entered list of seq?

Re: Condition to exclude data

Thu, 26 May 2016 20:10:28 GMT

Sorry for the delay with the response.

I had another challenge that now the user would like to have a dynamic option of changing the Seq range.

An instance like if a user would like see only Seq between 1,2,3,4,5,6.

Any advise please.

Thanks in advance with the help.

Re: Condition to exclude data

Thu, 26 May 2016 23:51:51 GMT

So the way i am thinking should is not sure if possible, but when a user select a value from the list box which has all the seq values from lets say 1 thru 10.

When a user will select 4 that should show all the test field values which has seq from 1 thru 4.

If this is quite not possible then some way where a user don't need to go for more clicks on the screen to render the specific seq range.

Hope i answered your question.

Thanks.

Re: Condition to exclude data

sunny_talwar — Fri, 27 May 2016 00:24:15 GMT

May be you need a And-mode selection. Look at this

Read about And-Mode here: AND-Mode

Re: Condition to exclude data

MarcoWedel — Fri, 27 May 2016 01:19:19 GMT

maybe using the principle of the As-Of-Table?

Re: Condition to exclude data

Fri, 27 May 2016 19:56:23 GMT

Thanks Sunny.

This might be it.

I will testing this in for next few days for the possibility integrating the change into Application.

Appreciate the help.

Thanks.