Count Customers with Similar Products

[#1]

Hello All,

I'm sure there is an easy way to do this, but I am having difficulty.  I would like to create a Flag that identifies what customers have the same products.  I am hoping to do this within the load script.  Hopefully, the following table will be clear:

Company	Fruit
Company A	Apples
Company A	Oranges
Company B	Strawberries
Company B	Grapes
Company C	Apples
Company C	Oranges
Company D	Apples
Company D	Grapes

In this scenario, I would like to know what Companies have both Apples and Oranges.  It's easy to see that the answer should be A&C, however, I am struggling to create the appropriate logic.  The formula I am using is returning A,C and D as well, when D only has apples and not both fruit.  It appears that my logic is keeping each type of fruit exclusive of the pair.

Hopefully, this was clear and someone can assist.

Thanks in advance.

TJ

[antoniotiman]

Hi Todd,

try this

Table:
LOAD * Inline [
Company, Fruit
Company A, Apples
Company A, Oranges
Company B, Strawberries
Company B, Grapes
Company C, Apples
Company C, Oranges
Company D, Apples
Company D, Grapes ];
Left Join LOAD Company,
If(Concat(Fruit,';',Fruit)='Apples;Oranges',1) as Flag
Resident Table Group By Company;

Expression : Only({<Flag={1}>} Flag)

Regards,

Antonio

[#3]

Hi Antonio,

I appreciate your response.

I probably should've mentioned that I was only creating a small example for time's sake. There would be 1000s of Companies for me to list.  Is there a way to do this without the manual inline table?  My Data is coming from a QVD.

Regards,

Todd

[sunny_talwar]

May be this

Count(DISTINCT {<Company = p({<Fruit = {'Apples'}>})*p({<Fruit = {'Oranges'}>})>} Company)

[#5]

Hi Sunny,

Appreciate your response as well.  I am not looking for a count of the Companies.  I am hoping to create a filter that can be applied to a table indicating what Companies have both Fruits.  I was thinking a flag in the load script would be most appropriate, but I could very well be wrong.

Regards,

Todd

[sunny_talwar]

Sorry, I missed the first few lines of your post. But the idea is that if you want to filter certain thing, you can use this in your expressions using this set analysis

{<Company = p({<Fruits = {'Apples'}>})*p({<Fruits = {'Oranges'}>})>}

Once you do that, it will only show those Companies which have both Apples and Oranges as Fruit

[#7]

Thanks again.  This is very helpful, however, I want my users to be able to turn the filter on / off at their discretion, then export/share the results for further management analysis.

[sunny_talwar]

Try the attached app and see if this makes sense

[antoniotiman]

Load * From Table.qvd (qvd);

instead of LOAD * Inline ...............