topic Re: Count Customers with Similar Products in App Development

Count Customers with Similar Products

Wed, 14 Jun 2017 18:55:58 GMT

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.

Re: Count Customers with Similar Products

antoniotiman — Wed, 14 Jun 2017 19:13:53 GMT

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

Re: Count Customers with Similar Products

Wed, 14 Jun 2017 19:30:37 GMT

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

Re: Count Customers with Similar Products

sunny_talwar — Wed, 14 Jun 2017 19:30:42 GMT

May be this

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

Re: Count Customers with Similar Products

Wed, 14 Jun 2017 19:35:03 GMT

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

sunny_talwar — Wed, 14 Jun 2017 19:36:46 GMT

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

Wed, 14 Jun 2017 19:42:52 GMT

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

sunny_talwar — Wed, 14 Jun 2017 19:48:45 GMT

Try the attached app and see if this makes sense

Re: Count Customers with Similar Products

antoniotiman — Wed, 14 Jun 2017 19:49:22 GMT

Load * From Table.qvd (qvd);

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