Cross Tab comparing commonalty of a dimension

[derekjones]

Hi

Scratching my head on this one. I'm looking to compare a single dimensio/field commonality for another field's use. I will use a simple example:

So I have orders and which countries they go to, like below:

Order	Country
1	UK
1	US
1	FR
2	UK
3	UK
3	US
4	FR
4	US
5	UK
5	US

I'm looking to do a cross tab of Country on horizontal and vertical to show the count of orders for each crossover of countries like this:

	UK	US	FR
UK	-	3	1
US	3	-	2
FR	1	2	-

But you can't put in the same dimension twice and the above is a very simple example, in reality the number of countries could be hundreds of different ones so I can't use set analysis to use where Country = 'xx'

Has anyone had experience doing anything like this before?

[captain89]

Hi, 

you can do it joining the same table with itself.

Orders1:
LOAD * INLINE [
Order, Country1
1, UK
1, US
1, FR
2, UK
3, UK
3, US
4, FR
4, US
5, UK
5, US
];

left join (Orders1)

Orders2:
LOAD * INLINE [
Order, Country2
1, UK
1, US
1, FR
2, UK
3, UK
3, US
4, FR
4, US
5, UK
5, US
];

Orders:
load *,
rowno() as Id
Resident Orders1 where Country1<>Country2 and isnull(Country2)<>-1 ;
drop table Orders1;

and then you use a pivot table... but i don't know your data set ... 

In this way you create a huge quantity of data so i don't know if it's the better solution for you.

See attachment

MP