Flagging non distinct numbers

[#1]

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.

[#2]

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

[swuehl]

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')

[CELAMBARASAN]

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

[#5]

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.

[swuehl]

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