5 Replies Latest reply: Jul 18, 2012 1:28 PM by Stefan Wühl RSS

    Flagging non distinct numbers

      Hello all, I know you can count distinct numbers/fields etc, but how do you go about flagging up non Distinct:- here is some data as an example.




      Customer Number | Property ref.

      001                      | 15   <-

      002                      | 16

      003                      | 15   <-

      004                      | 17


      Customer 001 and 003 have same property number, I want to 'Flag' these if they occur more than once, can anyone please provide formulae for such example please.



        • Re: Flagging non distinct numbers
          Rahul Lakhina

          try the folloing in the script:   if(FiledName = previous(FieldName), 'N','Y') as Distinct  Regards  R

            • Re: Flagging non distinct numbers
              Celambarasan Adhimulam


              For do it in load script








                        [Property ref.],

                        If(Count([Property ref.]) > 1, 1, 0) AS Flag \\1 for non distinct, 0 for distinct



                   Group by [Property ref.];


              Hope it helps

            • Re: Flagging non distinct numbers
              Stefan Wühl

              There are probably multiple ways to achieve what you want, load script based and front end only.


              For example, you could create a list box with an expression (select <expression> from field list), then enter as expression:


              =aggr(if(count([Property ref.])>1,[Customer Number]),[Customer Number])



              Or create a list box for field [Customer Number], then add an expression (from expression tab):


              if(count([Property ref.])>1,'Multiple','Once')

              • Re: Flagging non distinct numbers

                Hi Swuehl,

                if(count([Property ref.])>1,'Multiple','Once') looks promising, but when I go to the list box I cannot search by multiple, is there a way to make this searchable?



                  • Re: Flagging non distinct numbers
                    Stefan Wühl



                    I noticed that my suggested expression won't work as provided (using just the customer as list box field).


                    So either create a flag in the script as suggested above, or do something similar in the front end, if needed:


                    =aggr(nodistinct if(count( PartNr)>1,'Multiple','Once'),PartNr)


                    Unfortunately, you can't search the expression values in a list box, but you can use a straight table with dimension Customer and above expression and make the expression searchable. But a straight table has some limitations and differences compared to a list box.


                    In a list box, you could create a combined Customer / Flag string to show and select from, maybe something like


                    =aggr(nodistinct num(CustomerNr,'000') &' - '& if(count( total<PartNr> PartNr)>1,'MANY','ONE'), PartNr,CustomerNr)


                    If you don't need to be selection sensitive, I would probably go for the script solution.