Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

fields related only to certain value

Hi Everybody,

Consider this table:

Field1Field2
A1
A2
A3
A4
B1
B2
B3
C1
C2
D1
E1
E2
E3

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

Any sugestion?

Tags (1)
5 Replies
MVP
MVP

Re: fields related only to certain value

Maybe using a set expression like

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

Not applicable

Re: fields related only to certain value

in load script

sinanozdemir
Valued Contributor III

Re: fields related only to certain value

Something like this:

Capture.PNG

And here is your data model:

Capture.PNG

Hope this helps.

MVP
MVP

Re: fields related only to certain value

another one could be

A:

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

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

Community Browser