topic Re: Where Clause in QlikView

Where Clause

Anonymous — Sun, 14 Jan 2018 16:46:28 GMT

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

Re: Where Clause

sunny_talwar — Sun, 14 Jan 2018 16:57:46 GMT

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

sunny_talwar — Sun, 14 Jan 2018 16:59:23 GMT

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

Re: Where Clause

Anonymous — Sun, 14 Jan 2018 17:13:04 GMT

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

Re: Where Clause

sunny_talwar — Sun, 14 Jan 2018 17:45:41 GMT

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

Re: Where Clause

maxgro — Sun, 14 Jan 2018 19:12:34 GMT

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;