topic Re: Exclude records in QlikView

Exclude records

pauledrich — Wed, 25 Sep 2013 18:23:39 GMT

I have a customer table where I would like to sum sales but only where the transaction has Item A only and not A,B,C or A D & E - every attempt returns where A is included in any transaction.

My expression Sum (<{Product={A}>}TransactionValue) displays all transactions where A has been purchased - any ideas gratefully appreciated.

Re: Exclude records

Clever_Anjos — Wed, 25 Sep 2013 18:27:32 GMT

"Item A" is another field?

Re: Exclude records

swuehl — Wed, 25 Sep 2013 18:33:44 GMT

Maybe something like this, assuming you have something like a TransactionID:

=Sum (<{Product={A}, TransactionID = {"=count(distinct Product)=1"}>} TransactionValue)

Re: Exclude records

Clever_Anjos — Wed, 25 Sep 2013 18:41:45 GMT

Sum (<{Product={A}-P({<Product={"B","C","D"}>})>} TransactionValue)

Re: Exclude records

martinpohl — Wed, 25 Sep 2013 19:01:47 GMT

As I can see you have a transaction table where there are various line for the product.

So I would concat the products for each order number (left join (transaction) load orderno, concat(Product) as Concatfield resident transactions group by orderno). There are all products concatenated, so you can check only these orders where only product a is in.

Check attachment.

Regards

Re: Exclude records

pauledrich — Thu, 26 Sep 2013 10:00:50 GMT

Hi - assume item A is under the heading product and some customers purchase more than one product B,C &D with one transaction number and one Total Amount. I am trying to only show customers that only bought product A and not any other product.