topic Re: Self Join a Field in QlikView

Self Join a Field

Fri, 20 Jan 2012 19:12:21 GMT

I have a table with two fields, contact ID and event ID. I'm trying to join the event ID to itself, per contact ID.

From the example below, I have one contact who has interacted with three events, A,B,C. I would like to build the event pairs as seen in the bottom table.

Contact ID	Event ID
1	A
1	B
1	C
Result I'm trying to create:
Contact ID	Event ID	Event ID
1	A	B
1	A	C
1	B	A
1	B	C
1	C	A
1	C	B

Re: Self Join a Field

pover — Fri, 20 Jan 2012 21:37:37 GMT

Here's an example of a left join that is later filtered to remove rows with the same values.

Karl

Self Join a Field

Fri, 20 Jan 2012 22:55:57 GMT

Thanks for your help and the sample, Karl. Although the example is interesting, it seems designed for a slightly different problem than the one I'm considering. I'm looking for an QV expression that will accept my two columns of data (contact ID and event ID) and produce all the event pair combinations that a given contact ID has interacted with.

The data will allow my team to segment our customer base by similarity of attended events. I think QV could save me hours of off-line time if I could find an answer.

Thanks.

Re: Self Join a Field

pover — Fri, 20 Jan 2012 23:26:41 GMT

Here's an example with your data. You would have to handle 2 catalogs for events because one table can't have 2 columns EventID. If this isn't work you are looking for, please post an example of a report you would use for your analysis to understand it better.

Karl