Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
You should be able to add an expression into your table like:
=SUM(IF(Product="Product1",Quantity))
Hope this helps,
Many thanks for reply.
There are actually two problems:
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,
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.
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