topic Re: fields related only to certain value in QlikView

fields related only to certain value

Fri, 08 Jan 2016 16:16:57 GMT

Hi Everybody,

Consider this table:

Field1	Field2
A	1
A	2
A	3
A	4
B	1
B	2
B	3
C	1
C	2
D	1
E	1
E	2
E	3

I need to return those rows who have only 1 or 2 in load script. In this case C and D.

Any sugestion?

Re: fields related only to certain value

swuehl — Fri, 08 Jan 2016 16:31:25 GMT

Maybe using a set expression like

Count( {<Field1 = e({<Field2 = {">2"}>}) >} Field1)

Re: fields related only to certain value

Fri, 08 Jan 2016 16:32:23 GMT

in load script

Re: fields related only to certain value

sinanozdemir — Fri, 08 Jan 2016 16:34:42 GMT

Something like this:

And here is your data model:

Hope this helps.

Re: fields related only to certain value

maxgro — Fri, 08 Jan 2016 21:54:48 GMT

another one could be

LOAD Field1, Field2

FROM [https://community.qlik.com/thread/198912] (html, codepage is 1252, embedded labels, table is @1);

Right Keep (A)

load Field1 Where match(Filter, '1-2', '1', '2');

load Field1, Concat(DISTINCT Field2, '-', Field2) as Filter

Resident A

Group By Field1;

Re: fields related only to certain value

sunny_talwar — Fri, 08 Jan 2016 22:08:34 GMT

Yet another one could be:

Table:

Load Distinct Field1

FROM Source

Where Field2 = 1 or Field2 = 2;

Fact:

LOAD Field1,

Field2

FROM Source

Where Exists(Field1);

now this is very well a sample but in your case you might have a lot to include and very less to exclude. So you can take an alternative route

Table:

Load Distinct Field1

FROM Source

Where Field2 > 3;

Fact:

LOAD Field1,

Field2

FROM Source

Where not Exists(Field1);

Benefit here is that for large dataset you will be able to avoid aggregation.

HTH

Best,

Sunny