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.

       

      Paul.

        • 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

              Hi,

              For do it in load script

                   Main:

                   Load

                        *

                   From

                        DataSource;

                   Flag:

                   Load

                        [Property ref.],

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

                   Resident

                   Main

                   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?

                 

                Paul.

                  • Re: Flagging non distinct numbers
                    Stefan Wühl

                    Paul,

                     

                    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.

                     

                    Regards,

                    Stefan