Skip to main content
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Count rows with certain values in two different fields

I have a table like this :


product 1 product 1 cost product 2 product 2 cost product 3  product 3 cost
apple 10 banana 10 pineapple 12
pineapple   apple 10 banana


banana   apple 10 pineapple 10


I'm trying to get a count of people who purchased BOTH apples and bananas, so it'd return "2".

How would I go about doing that? I can't control which product is product 1, product 2, or product 3

Labels (4)
2 Replies

Does each row represent a customer who bought fruit?

In that case it would return all 3 rows and not 2. 

And if that is the case then something like this would return only the rows that have both apple and banana

load *
WHERE wildmatch(chiave,'*banana*')>0 or wildmatch(chiave,'*apple*')>0 ;
load product1 & product2 & product3 as chiave, *;
product1,product1 cost,product2,product 2 cost,product3, product3 cost


Otherwise it's not clear what you need.



Contributor II
Contributor II

Yes each row is 1 customer. They only bought the product if there's something in the product cost field, if that makes sense. It's not a great data set I know.

So if there's a product cost for apple/banana, that row would be counted.

I also tried to figure out how to do a conditional in the load script and just create new fields and populate based on:

if product 1 is apple, then new field apple_amount is product 1 cost

But I wasn't really sure how to do that either.