topic Re: Set Analysis - Intersection in QlikView

Set Analysis - Intersection

Fri, 24 Jun 2011 19:21:19 GMT

Hi All,

I am having a scenario where i am creating two sets SET1 and SET2 from a single table. I would like to perform set intersection on these two sets and i don end up in a result. The intersection does not happen.

I would also like to know some basics about how qlikview handles sets for intersection. Like Whether intersection can only be done on resident objects.

Should an intersection field be definitely used for joining two tables in the design..etc...

Any point regarding this will be more helful on this.

Can anyone help me on this.

Thanks

Hari

Re: Set Analysis - Intersection

johnw — Fri, 24 Jun 2011 20:04:15 GMT

I'm not sure I understand the problem or the question. You're doing something like this?

sum({<your first set>*<your second set>} Sales)

And you're NOT getting the intersection of these two sets?

What is your actual expression?

What do you mean by "resident objects"?

What do you mean by an intersection field?

Re: Set Analysis - Intersection

Tue, 28 Jun 2011 07:24:07 GMT

Hi John.

I am sorry if i was not clear with my explanation. Please find attached my sample application.

In the first sheet(Tables Available) I have three of tables.

Table 1: Event - Has got records for an event name(A,B), place of event and the zipcode of th event location.

Table 2: Distance - I have the Distance details for a zipcode with respect to other zipcodes.

Table 3: Distance_Limit - I have created a distance limit for each of the event (For A it is 96 and for B it is 50).

Now i have created two bookmarks Book1 which gives me the details of Other zipcodes for A within its distance limit (i.e 96)

and Book2 giving me the details of the Other zipcodes for B within its distance limit(i.e.50). Please see Zipcodes_Within_Limit table in Sheet2(Main) for this.

Selection to be Made for Book1:

Event_Name = A.

Selection to be Made for Book2:

Event_Name = B

These two Bookmarks have got some Zipcodes in common.

I am looking for these common zipcodes which is present in both the bookmarks.

I have created a table(Intersecting Zipcodes) in Sheet2(Main) with an Intersection Expression, which does not work for me.

Please let me know if you have understood what i am looking for .

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Tue, 28 Jun 2011 11:04:34 GMT

Hi,

Try this out.

count({<Other_Zipcodes = p({Book1})*p({Book2}) >}1)

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Tue, 28 Jun 2011 11:54:44 GMT

Hi Kaushik,

Thanks for your response. I tried ur expression, but could not find any result. I have used Other_Zipcodes as the dimension and used ur expression. Please let me know if i had tried the right way or not.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Tue, 28 Jun 2011 11:58:46 GMT

Hi,

Yes you did the right thing.

It worked at my side.

Here is an attachment

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Tue, 28 Jun 2011 12:20:04 GMT

Hi kaushik,

Thanks for this.

I have a doubt here. For Book1, The Other_Zipcodes Field does not have a value of "100" and for Book2, the Other_Zipcodes field does not have a value of "201". But in the resultant intersection table we have got both 100 and 201 values for the Other_Zipcodes dimension field.

My doubt is, these two codes(100 & 201) doesnt get intersect between the two Bookmarks, but is available in the sheet2 result table.

Please let me know on this.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Tue, 28 Jun 2011 17:20:08 GMT

Hi,

Did you check the sample qvw i have uploaded.

in that you will see the chat in second sheet is having the intersection of both the bookmarks.

i.e the chart do not thave 100,201 rather they have all values from 1 to 20.

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Wed, 29 Jun 2011 06:36:15 GMT

Hi Kaushik,

I have re-attached the application you have uploaded. In this i could fine both 100 and 201 zipcodes in the second sheet. Please check this and let me know if i am going wrong somewhere.

Thanks for your help.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Wed, 29 Jun 2011 06:39:17 GMT

Hi,

I have checked, but i cant see the values 201 and 100... i can see only 1 to 20.

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Wed, 29 Jun 2011 06:59:57 GMT

Hi Kaushik,

Please find the image attached from the application. Here i could find both the zipcodes(100 and 201) in the Other_Zipcodes dimension. I think i am missing something. Please check.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Wed, 29 Jun 2011 07:05:47 GMT

Hi,

I think due to the file i have uploaded had same name and you have download at the same loacation it is not updated..

In the file i recetly uploaded you can see the full table where you can see the 1 to 20 only within table only.

Here is the another file.

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Wed, 29 Jun 2011 07:36:22 GMT

Hi Kaushik,

I am really sorry abt this. I jus downloaded the appln in a seperate location and accessed it. But could see both the zipcodes there. Can you please provide me a screenshot of the appln and tha ppln itself with a diff name. I will check here where I am going wrong.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Wed, 29 Jun 2011 07:50:11 GMT

Hi,

Here is the screenshot of that.

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

Wed, 29 Jun 2011 11:19:09 GMT

Hi kaushik,

Nice i could see what i am looking for , but i am not able to get this from the application . Could not guess the reason. Can you please rename the appln and post it here. Thanks for this.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Wed, 29 Jun 2011 11:22:58 GMT

HI,

Gr8 finally u got what you wanted.

Here is your Application with diff name.

Rergards,

Kaushik Solanki

Re: Set Analysis - Intersection

Thu, 30 Jun 2011 07:31:28 GMT

Hi Kaushik,

Thanks a lot for your responses.

I am using Qlikview 8.5 version and i think that was the reason, why the P() does not work in mine. Please let me know your version of Qlikview and also if there is any other way of getting the intersection results.

Thanks

Hari

Re: Set Analysis - Intersection

kaushiknsolanki — Thu, 30 Jun 2011 07:40:39 GMT

Hi,

Yes may be that can be a reason.

I am using qlikview v10 SR 2.

I dont know. i have to try to get this without p().

Will post as soon as i get solution.

Regards,

Kaushik Solanki

Re: Set Analysis - Intersection

johnw — Wed, 06 Jul 2011 17:00:53 GMT

Perhaps this:

count({Book1} Other_Zipcodes)>0 and count({Book2} Other_Zipcodes)>0

Re: Set Analysis - Intersection

Fri, 08 Jul 2011 13:17:53 GMT

Hi John,

Thanks for this.

I tried with the expression. It works fine when i don use any filters in my expression when creating the bookmarks. If i have any filter condition in creating my bookmark then this does not work. Please let me know on this.

Thanks

Hari