9 Replies Latest reply: Mar 16, 2016 4:14 AM by Boris Adloff RSS

    Duplicates counting and flagging

    Boris Adloff

      Dear all,

       

      I got stuck on what seemed a simple problem.

      I have a table A with fields

      A.id, ... ,

      A.text

       

      Lets say that it looks like this:

      A.idA.text
      1ALPHA
      2BRAVO
      3ALPHA
      4CHARLIE

       

      In a pivot chart, I would like to show, which texts have duplicates and count the number of duplicates:

       

      A.textFlag multiple

      ALPHA

      1
      BRAVO0
      CHARLIE0
      SUM1

       

      I tried if-clauses:

      if( count ({<...>}A.text)  - count (distinct {<..>}A.text)=0, 0,1)

       

       

      Nice and simple. That does in fact deliver the correct result - but it fails to work in summing up. As QV considers the if-clause for all the datasets above, I will always get a "1" instead of the number of terms with duplicates ("Doubletten").

       

      So, I also tried aggr with an aggregation level ("Team number") several tables away

      sum(aggrif( count ({<...>}A.text)  - count (distinct {<..>}A.text)=0, 0,1) ), A.text)

      And here it starts getting messy, as some values are correct, some are not and the whole thing changes with the selections I make. Weird. Unfortunatly, I can not post the data here...

       

      I thought of mapping the data into a separate table by script, but there must be a somewhat more elegant way to this.

      Have I overlooked a much more simple way?

      How would you approach such a task?

      Any help greatly appreciated,

       

      best regards,

      Boris