Finding all the Speed which is sold by all partner on same date

[#1]

Hi

Date	Speed	Partner	Quantity
20-05-2016	5000rpm	ABC	100
20-05-2016	7200rpm	XYZ	100
20-05-2016	7200rpm	ABC	200
20-05-2016	5000rpm	XYZ	200
20-05-2016	5000rpm	PQR	300
21-05-2016	18000rpm	ABC	300
21-05-2016	5000rpm	PQR	400
21-05-2016	7200rpm	ABC	500
21-05-2016	7200rpm	PQR	500

I need to find which speed is sold by all the partners whose Quantity is greater than zero on the same data.

From the above example answer is 5000rpm.

Any solution for the above question?

Thanks

Piyush

[#2]

5000rpm is sold by all the partners ABC,XYZ,PQR on same date (20-05-2016)

[Kushal_Chawda]

stalwar1‌, I remembered, you had a same problem sometime back and swuehl  provided the solution, it seems like the same problem, you can share that link as well

[#4]

I need to use this as a filter..so cannot add dimension date

[Kushal_Chawda]

That's not the problem, you can select the Date, it will give you results. Just create the straight table as I have mentioned

[sunny_talwar]

May be modify your script like this:

Table:

LOAD Date,

     Speed,

     Partner,

     Quantity,

     AutoNumber(Date&'|'&Speed) as Key

FROM

[https://community.qlik.com/thread/217607]

(html, codepage is 1252, embedded labels, table is @1);

and then try this expression:

=Concat(DISTINCT {<Key= {"=Count(DISTINCT Partner) = Count(TOTAL DISTINCT Partner)"}>}Speed)

UPDATE: Sample attached

[#7]

Hi Sunny,

I need to do it on UI. Because this i need to create as a filter and It is kind of dynamic based on some more filters.

Thanks,

Piyush

[sunny_talwar]

This is all front end my friend. We are just creating a field to make it more efficient. The other option would be using Aggr() which would be slower. Is slowness not a problem?

[#9]

Can you please let me know using aggr() function?

[sunny_talwar]

Here you go:

=Concat(DISTINCT If(Aggr(Count(DISTINCT Partner) = Count(TOTAL DISTINCT Partner), Date, Speed), Speed))

[#11]

Thanks Sunny.

Now need to add now some more conditions into this like sum(Quantity)>0 and rest all speed should go into 'Others' as a field.

Thanks,

Piyush