Join messed up because more than 1 record for the field i am joining

[maygross]

Hi,

Basically I'm joining two tables, one of them has subscriber numbers and payment amount and the other has subscriber numbers and model type. In the second table, one subscriber could have more than one model type. When I join, the payment amount is now duplicated because one subscriber has two model types so the payment amount section is incorrect when summarized. I dont care which model type is selected, but how do i make it so only one is selected and joined?

[binuacs]

@maygross If you don't consider the model number you can use the summarise tool or sample to achieve the result

[Kenda]

@maygross 

You could also use a Sample tool on your table 2 and keep only the first 1 row and group by sub # prior to joining to the other table. This will result in just one record per sub # from that dataset. 

[maygross]

the whole point is that i need all three columns though... i cant exclude the model type

[Kenda]

@maygross You will still have model type. You will just keep the first record for each Sub #. Try it out and let us know if that's what you're looking for.

[nagakavyasri]

[maygross]

How do I do the sample tool when my data is inDB?

[Kenda]

@maygross 

A similar result can be achieved using the Summarize In-DB tool, grouping by Sub # and selecting First for model type.

[hlee36]

"Unique" tool will do the job. You can still keep your workflow and just add the "Unique" tool (Or you can put "Unique" tool to the second dataset before joining). Make the "sub#" field as unique, then it will remove the duplicates. Keep in mind that the model type will be the first model type in the original data in case of multiple model types. 

[maygross]

unfortunately i am using in DB so the unique tool wont work for me here... otherwise i would have used that. do you have a similar suggestion for in db?