Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);
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);
Thank you. But it there a solution with and / or?
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);
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;