Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dibbi7777
New Contributor II

Where Clause

Hi,

I have a question regarding to a where clause.

Here is my table

a/a/a

a/a/b

a/e/r

a/q/q

a/u/t

How must look my where clause that i get only back rows which an element is max 1 time in a row.

In the example above the correct result must be a/e/r and a/u/t.

Thank you

5 Replies

Re: Where Clause

May be this

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

Re: Where Clause

Here is a sample script using an inline load

Table:

LOAD * INLINE [

    Field

    a/a/a

    a/a/b

    a/e/r

    a/q/q

    a/u/t

]

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

dibbi7777
New Contributor II

Re: Where Clause

Thank you. But it there a solution with and / or?

Re: Where Clause

Not sure what you mean? I have used and

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

MVP
MVP

Re: Where Clause

another approach for variable number of elements

Table:

LOAD Field, SubField(Field, '/') as Word INLINE [

    Field

    a/a/a/a

    a/a/b

    a/e/r

    a/q/q

    a/u/t/t

    a/u/z

];

TableF:

LOAD

     *

WHERE WordCount = WordCountDistinct;

LOAD

     Field,

     Count(Word) as WordCount,

     Count(DISTINCT Word) as WordCountDistinct

Resident Table

Group By Field;

Community Browser