Problem with frequency

[qw_johan]

Hi

I have a problem checking for frequency. I need to know how many times a, b & c have been together.

a->b and b->a should both count as being together for a&b. The result I want is:

a&b = 3

b&c = 1

c&a = 2

MyTable:

IN	OUT	TIMESTAMP
a	b	2010-06-18 09:34:12
a	b	2010-01-17 18:25:55
b	c	2010-01-17 18:48:24
c	a	2010-01-17 12:09:05
b	a	2010-01-17 18:20:48
a	c	2010-01-17 18:26:37

Thanks,

johan

[#2 · Accepted solution]

Create a Straight Table with a Calculated Dimension:

=If(IN < OUT

     , IN & ' & ' & OUT

     , OUT & ' & ' & IN)

This will ensure that b & a is the same as a & b

Expression is simply Sum(1).

-John

[qw_johan]

Thanks for your quick reply.

That REALLY worked like I wanted it too. Thanks!

a, b, c ... etc represents ip numbers...but that shouldn't make a difference in the expression?

And I have around 100 000 records in my table...using an "if..." expression still ok?

Thanks again John

[#4]

You're welcome.  It should work with IP addresses just fine. 

Whether the calculation is too much with 100,000 records depends on what else you're doing. 

But if performance suffesr because of the calculated dimension, move it into the load statement so you can use a regular field in your table.