8 Replies Latest reply: Jun 14, 2017 3:49 PM by Antonio Mancini

# Count Customers with Similar Products

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.

TJ

• ###### Re: Count Customers with Similar Products

Hi Todd,

try this

Table:
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 ]
;
If(Concat(Fruit,';',Fruit)='Apples;Oranges',1) as Flag
Resident Table Group By Company
;

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

Regards,

Antonio

• ###### Re: Count Customers with Similar Products

Hi Antonio,

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

• ###### Re: Count Customers with Similar Products

May be this

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

• ###### Re: Count Customers with Similar Products

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

• ###### Re: Count Customers with Similar Products

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

• ###### Re: Count Customers with Similar Products

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.

• ###### Re: Count Customers with Similar Products

Try the attached app and see if this makes sense