2 Replies Latest reply: Mar 5, 2014 6:50 AM by Mike Swinn RSS

    Using Brackets to Define Precedence in Set Analysis

    Mike Swinn

      I am trying to get a distinct count of people belonging to group X and Y but not Z. People can belong to multiple groups and the data structure is such that there are two columns and people can have multiple records, e.g:

       

      Name     Group

      A               X

      A               Y

      A                Z

      B               Y

      C               X

      C               Y

       

      So in this example the result should be equal to 1, since only C satisfies the condition.

       

      However, I am not sure how to specify the order such that QlikView calculates it as ((A and B) but not C) rather than (A and (B but not C))

       

      To get A and B I use:

          

      count(

      {$

        <Name = P({$<Group = {'Y'}>})>

        * <Name = P({$<Group = {'X'}>})>

      }

      DISTINCT Name

      )


      And adding the not C should be:


      count(

      {$

        <Name = P({$<Group = {'Y'}>})>

        * <Name = P({$<Group = {'X'}>})>

        - <Name = P({$<Group = {'Z'}>})>

      }

      DISTINCT Name

      )

       

      In this case the precedence appears to work out the terms in the order I want, and the qlikview help on set analysis suggests that this would be the case as intersections are evaluated before exclusions. However, as a general rule I would like to be able to use brackets to be certain, and for more complex cases which go against the default precedence. Yet if I wrap the first two terms in brackets it does not evaluate at all:

       

      count(

      {$

        <Name = P({$<Group = {'Y'}>})>

      ( * <Name = P({$<Group = {'X'}>})>

        - <Name = P({$<Group = {'Z'}>})>)

      }

      DISTINCT Name

      )

       

      How can I get this working? The set analysis help states that normal brackets should work, but in the examples it uses this inside a single term, and does not work for what I want to do.

       

      Edit: Added specific example

        • Re: Using Brackets to Define Precedence in Set Analysis
          Tresesco B

          Probably, correcting the parenthesis would work. Try like:

           

          count(

          {$

            <Name = P({$<Group = {'Y'}>})>

          * (<Name = P({$<Group = {'X'}>})>

            - <Name = P({$<Group = {'Z'}>})>)

          }

          DISTINCT Name

          )

            • Re: Re: Using Brackets to Define Precedence in Set Analysis
              Mike Swinn

              Very interesting, I hadn't actually wanted the brackets around the latter terms, but your suggestion did produce a valid result in its own right.

               

              But in some cases I would need to use the brackets on the first terms so was wondering how to do this. I tested further and it seems that QlikView doesn't like a bracket right at the start of the sequence. So I tested by adding a dummy term at the start which always evaluates as true, then intersected this with the the remainder of the terms which I enclosed in brackets in the order I had wanted.

               

              Specifically I used:

               

               

              count(

              {$  <Name = >

                *

                ((<Name = P({$<Group = {'Y'}>})>

                * <Name = P({$<Group = {'X'}>})>)

                - <Name = P({$<Group = {'Z'}>})>)

              }

               

               

              DISTINCT Name

              )

               

               

              The blue text is the dummy term, green is the outer brackets used to enclose the remainder and red is the inner brackets which I originally wanted.

               

               

              I'll test on a more complex dataset to see if the logic works in all cases, but this does appear to be what I needed. Thanks for the help.