5 Replies Latest reply: Jan 14, 2018 2:12 PM by Massimo Grossi RSS

    Where Clause

    Dave Loop

      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

          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
            Massimo Grossi

            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;