2 Replies Latest reply: Jan 19, 2011 7:52 PM by John Witherspoon RSS

    Set Analysis nulling itself

      I need to extract data from two tables for a KPI with:

      - funcs may have several stats

      - stats may have XPTO on a field (ie.: asda sdXPTO adasd)

       

      I want those funcs that have at least ONE stats record that contains 'XPTO' AND none that doesn't contain 'XPTO'

       

      In SQL it would be something like:

       

      SELECT distinct
      f.num_seq from func f, status s
      where
      f.funcid = s.funcid
      and
      f.func = 'ABC'
      and num_seq in (
      SELECT
      f.num_seq from func f, status s
      where
      f.funcid = s.funcid
      and
      f.func = '1A.1.1.1.10.3.5.3.'
      and s.spec like '*XPTO*'
      )
      and num_seq not in (
      SELECT
      f.num_seq from func f, status s
      where
      f.funcid = s.funcid
      and
      f.func = '1A.1.1.1.10.3.5.3.'
      and s.spec not like '*XPTO*'
      )



       

      I was trying with Set Analysis with this:

      {$<SPEC={'*XPTO*'}>-<~SPEC={'*XPTO*'}>}

      But apparently the two sets are nulling each other.

       

      Any thoughts?

       

        • Set Analysis nulling itself

          So here's some more information and an example that might help you to help me :).

           

          I've made a file that has just some minor info but that allows me to do the tests that I need. The file is attatched to this reply.

           

          The problem is that all the combination operators are working like they showld except for the one that i need in this case.

           

          I want to have a count of the Funcs tha have booth a stat with risk and another one without risk, but it simply isn't working. The value that showld be showing in the * column showld be 2 but it's allways 0.

           

          Can somebody help please?

           

          Thanks in advance!

            • Set Analysis nulling itself
              John Witherspoon

              Thanks for the sample file. Unfortunately, I can't figure out why it's failing. It seems simple, and seems like it should work. I'm probably just missing something. But there's a small chance that it's a bug, and it happens because some programmer thought they were being clever, and that if you selected A and then selected NOT A, that these sets were mutually exclusive, so it doesn't even bother with the sets. And that would be true... IF we were just talking about a single field, but not when we're talking about related data, since in your case, for instance, you have functions that are related both to A and to NOT A, and so should still be in the intersection of the two sets.

              The symmetric difference (/) appears to work properly, though, which gives us a workaround, since the whole minus the symmetric difference is the intersection:

              count(distinct funcId) - count({<description*={"*risk*"}> / <description-={"*risk*"}>} distinct funcId)