4 Replies Latest reply: Apr 4, 2013 11:04 AM by Dave Riley RSS

    Help determining the subset of data that had never a certain value

    Paulo Barbosa

      Dear all,

      We need to find, the “Set” of data that had never had a certain value.

      Lets say we have the following input table listing the V_Values that F_Filed has assumed:

       

      F_Field

      V_Value

      m1

      A

      m1

      B

      m2

      A

      m2

      C

      m3

      B

      m4

      B

      m5

      B

       

      Case1) If we want to list all F_Fields that had never got “A” value; the set would be only m2.

       

      Case2) If we want to list the F_Fields that  never had a  “C”; the set would be m1,m3,m4,m5

       

      The only way we fount to implement this  in Qv, was using a “dynamically built” set analysis. For instance, for the case “never been “A”, it would be:

       

      concat({<F_Field-=$(='{'&concat(if(V_Value='A', F_Field),',')&'}') >} Distinct F_Field, ' ')

       

      It can even be used into a calculated dimension:

       

      aggr(concat({<F_Field-=$(='{'&concat(if(V_Value='a', F_Field),',')&'}') >} Distinct F_Field, ' '),F_Field)

       

       

      As we  would expect, this approach worked  fine for a relatively low number of different F_Fields (lets say less than 6k different F_Filels), but our production DB has about 1.6 M distinct F_Fields and about 30M rows.

       

      What can we do to achieve our buckets of data ?

       

      Best regards,

      Paulo