Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below table shows the source and destination information. I would want to count the no. of instances of travel between 2 cities.
The desired answer is:
US - Canada = 3
US - Denmark = 2
US - UK = 1
US - Brazil = 1
Put
Source and Destination as Dimension
Count(distinct Destination) as Expression
Maybe like this
LOAD
Source,
Destination,
If(Source precedes Destination, Source & '-' & Destination,Destination &'-'&Source) as Travel
FROM ...
Then create a chart with dimension Travel and expression
=Count(Travel)
This is amazing
I guess the MaxString() or MinString() can also work here, right?
UPDATE: RangeMaxString() or RangeMinString()
UPDATE2: Something like this:
Table:
LOAD *,
RangeMaxString(Source, Destination) & '-' & RangeMinString(Destination, Source) as Travel;
LOAD * Inline [
Source, Destination
US, Canada
US, Denmark
Canada, US
Denmark, US
UK, US
US, Canada
US, Brazil
];
UPDATE3: