Duplicates in other fields after subfield-ing

[#1]

I have the following columns:

Customer Name	Level	Product Purchased
ABC	A	Eggs
ABC	A	Bacon
DEF	C	Cheese
GHI	A	Perfume
GHI	A	Body Spray

Initially I had the string values for 'Product Purchased'  (e.g. 'Eggs,Bacon' for Customer 'ABC') so I used the subfield function during my load, this gave me duplicates in 'Level' and 'Customer Name'.

I want to perform some analysis like the count of levels by Customer Name, but it counts 'A' as 4 as there are four records of 'A' instead of '2'.

what is the best way to do this?

[rubenmarin]

Hi Joanne, maybe counting the distint customers by level?, like:

Count(distinct [Customer Name])

[Anonymous]

Ruben's response works.

Otherwise, you would want to normalize your data and in the data model split the Products Purchased into another table.

So you would end up with a customer table with 1 row per Customer.  And then a Product Purchased tables that could have multiple rows per customer.