Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count duplicate items in a flat table

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

1 Solution

Accepted Solutions
Not applicable
Author

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,

View solution in original post

5 Replies
Not applicable
Author

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

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

Hope this helps,

Not applicable
Author

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?
Not applicable
Author

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,

Not applicable
Author

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.

Not applicable
Author

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