Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Duplicates in other fields after subfield-ing

I have the following columns:

Customer NameLevelProduct Purchased
ABCAEggs
ABCABacon
DEFCCheese
GHIAPerfume
GHIABody 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?

2 Replies
rubenmarin
Not applicable

Re: Duplicates in other fields after subfield-ing

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

Count(distinct [Customer Name])

atkinsow
Not applicable

Re: Duplicates in other fields after subfield-ing

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.