Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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 AApples
Company AOranges
Company BStrawberries
Company BGrapes
Company CApples
Company COranges
Company DApples
Company DGrapes

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

8 Replies
antoniotiman
Honored Contributor III

Re: Count Customers with Similar Products

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

Not applicable

Re: Count Customers with Similar Products

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

MVP
MVP

Re: Count Customers with Similar Products

May be this

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

Capture.PNG

Not applicable

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

MVP
MVP

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

Not applicable

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.

MVP
MVP

Re: Count Customers with Similar Products

Try the attached app and see if this makes sense

Capture.PNG

antoniotiman
Honored Contributor III

Re: Count Customers with Similar Products

Load * From Table.qvd (qvd);

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