Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,', ')
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,', ')
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.