topic Set analysis and operator in QlikView

Set analysis and operator

sifatnabil — Mon, 18 Apr 2016 11:06:57 GMT

Hi all,

I would like to count the distinct number of sessions where trader A and B both participated on a Tuesday. Should the set analysis look like this?

count({<SessionId = p({<[TraderName] = {'Mark'}>}) * p({<[TraderName] = {'Bob'}>}),Day={'Tues'}>} distinct SessionId)

Re: Set analysis and operator

tresB — Mon, 18 Apr 2016 11:26:24 GMT

May be like:

count({<[TraderName] = {'Mark'},Day={'Tues'}> *<[TraderName] = {'Bob'}>},Day={'Tues'}>} distinct SessionId)

Re: Set analysis and operator

sunny_talwar — Mon, 18 Apr 2016 11:29:30 GMT

What about this?

Count({<SessionId = p({<[TraderName] = {'Mark', 'Bob'}, Day={'Tues'}>})>} distinct SessionId)

Re: Set analysis and operator

sifatnabil — Mon, 18 Apr 2016 12:58:52 GMT

Hi sunindia‌, this is close, but it's doing Mark OR Bob. How can we make it count sessions for Mark AND Bob ?

Re: Set analysis and operator

sunny_talwar — Mon, 18 Apr 2016 13:01:45 GMT

tresesco‌ expression did not work as well?

Re: Set analysis and operator

sifatnabil — Mon, 18 Apr 2016 13:02:40 GMT

Yes, that one seems to return zero.

Re: Set analysis and operator

sunny_talwar — Mon, 18 Apr 2016 13:05:51 GMT

What about this:

Count({<SessionId = p({<[TraderName] = {'Mark'}, Day={'Tues'}>}) * p({<[TraderName] = {'Bob'}, Day={'Tues'}>})>} distinct SessionId)

Re: Set analysis and operator

kkkumar82 — Mon, 18 Apr 2016 13:13:19 GMT

I think it would be better to create a sample either by some one to see the results

Re: Set analysis and operator

sifatnabil — Mon, 18 Apr 2016 13:16:14 GMT

Thanks, I just checked the data and it looks like both these variations work:

count({<SessionId=p({<[TraderName] = {'Bob'},Day={'Tue'}>})*p({<TraderName={'Mark'},Day={'Tue'}>})>}distinct SessionId)

count({<SessionId = p({<[TraderName] = {'Bob'}>}) * p({<[TraderName] = {'Mark'}>}),Day={'Tue'}>} distinct SessionId)

Any idea why tresesco's doesn't work? Is it because TraderName itself cannot be 2 different names simultaneously?

Re: Set analysis and operator

sunny_talwar — Mon, 18 Apr 2016 13:20:27 GMT

Yes, you are right, a single row of data cannot have both TraderName on it. But a single SessionId can have multiple rows where one contains Mark and other contains Bob. He might have forgot to add SessionId and the p.