Count rows with certain values in two different fields

[hucand]

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	13
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

[aguirre]

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, *;
LOAD * INLINE [
product1,product1 cost,product2,product 2 cost,product3, product3 cost
apple,10,banana,10,pineapple,12
pineapple,,apple,10,banana,13
banana,,apple,10,pineapple,10
];

Otherwise it's not clear what you need.

[hucand]

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.