Count duplicate items in a flat table

[#1]

Hi experts,

I have a question to count duplicate items in a flat table.
My flat table basically consists of 3 fields, userID, product and quantity
i.e.

user1, product1, 1
user1, product2, 2
user2, product1, 1
user3, product1, 1

I want to count the number of users with duplicate products 1 and 2.
What I did in Access is to create an additional pivot table with field userID, 'product1 quantity' and 'product2 quantity', then get the following pivot table

user1, 1, 2
user2, 1, 0
user3, 1, 0

Then I can identify the duplicate users and count them.

I wonder how to do this in QlikView either using expression or script.

Thanks

[#2]

You should be able to add an expression into your table like:

=SUM(IF(Product="Product1",Quantity))

Hope this helps,

[#3]

Many thanks for reply.

There are actually two problems:

• Can I count users with duplicate products in 1 step from a flat table without creating a pivot table? and how
• If 2 steps needed, how to read data from the pivot table and use in other tables to do counting?

[#4 · Accepted solution]

I think you are better off doing this in the script then, I haven't used this code before so forgive me if there are any mistakes but here goes:

UserProductCounts:

LOAD User, Sum(Quantity) AS ProductOneCount From ................. WHERE Product='Product1' Group By User

UserProductCounts:

CONCATENATE(UserProductCounts)

LOAD User, Sum(Quantity) AS ProductTwoCount From ................. WHERE Product='Product2' Group By User

Try this, as I say I haven't used it before but it should be relatively close to what you need.

Cheers,

[#5]

Yes, this is very close to what I need. By creating a new table that stores the product info and linking back via userID, this code works for me.

Many thanks, Nigel.

[#6]

Hi,

Another solution for this problem:

LOAD
user,
sum(if(product='product1',qty)) as p1_qty,
sum(if(product='product2',qty)) as p2_qty
FROM...
GROUP BY
user;

Thanks

Amit