Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

May be this

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

sunny_talwar

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);

Anonymous
Not applicable
Author

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

sunny_talwar

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);

maxgro
MVP
MVP

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;