Hello,
I am trying to use the Unique tool to identify duplicates based on 3 fields (one text field, one date field, one numeric field.) In other words, my key is a combination of the three fields. When I execute the canvas, the tool executes but, when I look at the duplicate result output, I see something like this:
Field 1 | Field 2 | Field 3 |
abc | 2/12/18 | 5 |
abc | 2/12/18 | 5 |
abc | 2/12/18 | 12 |
So, for some reason, the numeric field is not being considered as a part of the key but the other two are. I know I have it checked in the tool configuration. Any ideas?
Thanks!
Solved! Go to Solution.
Could you provide a small sample of the data?
Obviously in your example, records 1 and 2 would be identical unless there are other fields
Yes, records 1 and 2 are identical but record 3 should not be there. I am not sure why this is happening considering I am forcing all three fields to be a combined key.
Here is a real "duplicate" output sample... the third column is, for some reason, just not taken into account for the key:
0191 2019-07-30 5
0191 2019-07-30 5
0191 2019-07-30 79.99
1936 2019-07-30 5
1936 2019-07-30 242.2
So if I run that through a unique tool and select all fields.
I get that the 2nd record is a duplicate. Would that not be what you expect?
Are you expecting that the VALUES in the records would be counted as duplicate?
If you select all 3 columns, then all 3 must be unique. Only record 2 matches record 1 entirely so it will be filtered as a duplicate
I was misunderstanding how the tool works. I thought the duplicate output would give me all records that appear more than once while the unique output would give me only records that appear exactly once. I am happy to say we figured out the problem! The tool goes through the data with the main goal of outputting a unique list of transactions (per the key). When it encounters a 2nd, 3rd, 4th, etc. instance of the "same" transaction, it sends it to the duplicate output. So, my third line was actually the 2nd instance of that key appearing while the 1st instance was captured in the unique output. Thanks for the help!
Well @RodiT let me help you 1 more time then!
If you look for the Crew Macro Pack, find the ONLY UNIQUE tool. That's what it does.
Any record that is a duplicate is sent to duplicate and ONLY true unique records are kept, not the first instance of a unique.
Hope that helps, have a great day!!