Intersection / Set analysis

[Anonymous]

I need some help with the following: My goal is to find clients who bought Cat1 product also bought Cat2 products.

1. I have the data in one table as give below:

Prod Cat	Prod Sub Cat	Client Add	MySales	TeamSales
Cat1	Prod1	Client1	100	100
Cat1	Other Products	Client1	0	200
Cat2	Prod3	ClentX	100	100
Cat2	Other Products	Client1	0	400
Cat2	Prod4	Client1	50	100

I need a output as the followng :

After selecting Cat1, I need to show only the following record. Any suggestions?

Cat2

Client1

50

500

thanks !

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[Anonymous]

This is great ! How do I add one more field called "zip code" in addition to ClientAdd if required. (See below)

Is the following correct? or do I have to use some other operator?

({<[Prod Cat]={'Cat2'},[Client Add]=p({<[Prod Cat]=p([Prod Cat])>}[Client Add]),[Zip Code]=p({<[Prod Cat]=p([Prod Cat])>}[Zip Code])>}TeamSales)

[Gysbert_Wassenaar]

I don't understand what you're trying to do. Can you explain what the result should be and why?

[Anonymous]

If I had an additional column called zip and would like to still get the same result.

Prod Cat	Prod Sub Cat	Client Add	Zip	MySales	TeamSales
Cat1	Prod1	Client1	10001	100	100
Cat1	Other Products	Client1	10001	0	200
Cat2	Prod3	ClentX	10009	100	100
Cat2	Other Products	Client1	10001	0	400
Cat2	Prod4	Client1	10001	50	100

Result:

Cat2

Client1

10001

50

500