Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i've got a simple excel, there are 4 columns and ~57000 rows:
ID | Vehicle | Start | Stop |
1 | Auto1 | Paris | Madrid |
2 | Auto1 | Madrid | Berlin |
3 | Auto2 | Madrid | Paris |
4 | Auto2 | Paris | Madrid |
5 | Auto3 | Berlin | Madrid |
1 row means 1 travel, for example in the first row Auto1 travels from Paris to Madrid.
I'd like to make a bar chart, where i'd like to count, how many Start-Stop relations i have. It doesn't matter if the Start and Stop location is reversed, if it's the same 2 location. In this little example, we would have bars with the following heights:
3 for Paris-Madrid bar (2 Paris-Madrid + 1 Madrid-Paris)
2 for Madrid-Berlin bar (1 Madrid-Berlin + 1 Berlin-Madrid)
I tried to solve this problem with a new master dimension:
if((Start='Paris' AND Stop='Madrid') OR (Start='Madrid' AND Stop='Paris'),'Paris-Madrid',(if....))
, but i have like 6-700 such relations, so it would be an endless expression.
Do you guys have any good idea?
Thank you in advance!
By using this script I easily get the table and chart below.
LOAD *,
RangeMinString(Start, Stop)&' - '& RangeMaxString (Start, Stop) as Travel
inline [
ID,Vehicle,Start,Stop
1,Auto1,Paris,Madrid
2,Auto1,Madrid,Berlin
3,Auto2,Madrid,Paris
4,Auto2,Paris,Madrid
5,Auto3,Berlin,Madrid];
By using this script I easily get the table and chart below.
LOAD *,
RangeMinString(Start, Stop)&' - '& RangeMaxString (Start, Stop) as Travel
inline [
ID,Vehicle,Start,Stop
1,Auto1,Paris,Madrid
2,Auto1,Madrid,Berlin
3,Auto2,Madrid,Paris
4,Auto2,Paris,Madrid
5,Auto3,Berlin,Madrid];