Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

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
Author

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)

Capture.PNG

Not applicable
Author

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

Not applicable
Author

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

Capture.PNG

antoniotiman
Master III
Master III

Load * From Table.qvd (qvd);

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