How to find if the same number is assigned to different IDs

[pranaview]

Hi Guys,

As the subject says, I need to find out if the same number from a field is being assigned to different IDs.

So basically my data looks like this

ID  Number

1     123

2     783

3     890

4     123

Not only do I need to show that which Number is being duplicated but also for which different IDs. So in the little example above, the same Number 123 is assigned to two IDs 1 and 4 and I have to show that to the user so they can take appropriate action.

Any suggestion will be appreciated as i need to quickly get this done.

Thanks!

[GaryGiles · Accepted solution]

It depends on how you need to present the data:

In a table chart, you use the following as a dimension to limit the list to only Numbers with multiple IDs:

=aggr(Only({$<Number={"=Count(Distinct ID)>1"}>}Number),Number)

Be sure to uncheck the "Include null values in the dimension definition.

Then, if you want to list the each ID on a separate row, just add ID and a second dimension.

If you want to list all IDs associated with a Number on the same row, you could add a measure with an expression of:

=concat(distinct ID,', ')

[pranaview]

Thanks for the response Gary!

This expression for a dimension does gives me the result that I was looking for but for some reason when I select the ID(second dimension) in the Table chart, all the records disappear from the table. 

I also checked using the Previous() function and comparing the Numbers after getting the table ordered by Number field which lets me create a duplicate flag of sort but i am not sure whether i should go with that or with the dimension expression in the frontend. But thanks for you solution, it really helped.