Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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