One thing you will need to do to the sample table of data that you put in your question is make it so that you have two ROWS for each path. You currently have two COLUMNS for each path (From State and To State).
For example, you have:
From State to State Alabama Alabama Alabama Alaska
And you need something more like this (you don't really need both from and to state in the same row in the final table, I just left them for reference):
From State to State Origin or Destination PathID Alabama Alabama
1 Alabama Alabama Destination 1 Alabama Alaska Origin 2 Alabama Alaska Destination 2
I've found that an easy way to convert your data structure to two rows for each path is to just add two new columns (Origin and Destination); give all rows in the Origin column a value of 1, give all rows int he Destination column a value of 2, and then pivot on these two rows in Tableau.
You can then create your unique ID using a calculated field of the from-to fields and it should work to make the map.
I have written up some information about how to create these maps here: Origin-Destination Maps (or Flow Maps)
I have tried that and i couldn't understand how to generate PathID for the data i have of 360*360 cities.
Looking for the logic which could help me to generate the pathID.
Thanks for the information. But i have 360*360 postcode city combinations and the rows to duplicate and assign the Origin and Destination flag and create PathID. Is there a way other than this to achieve this using some table calculation method!
No matter where you do the prep of the data (adding a column in Excel and pivoting in Tableau desktop, using Maestro (in Beta), writing a Python script, etc.) in order to draw an origin destination map you will need one row for every stop along the line. If you just have two points in every line (origin and destination), each unique path will require TWO rows.
In Tableau desktop you can't pivot on calculated fields (to the best of my knowledge). I believe you can pivot on calculated fields in Maestro if you want to try the Beta of that for your data prep (Welcome to Tableau's Pre-Release and User Research Community ) - I will try that out later today to confirm.
Even with 360*360 combinations (I assume this means you have a table with ~129k rows) you can probably create the new fields in Excel in about a minute. Then take the file back into Tableau and do the pivot.
Otherwise, you could write a python script that would re-organize the data so that each origin-destination pair on a row was split into two rows, but I would bet that would take longer than using Excel (or probably even longer than signing up for the Beta program, downloading and installing Maestro, and doing it there )
Once your data is prepped (two rows for every path), the rest is all table calculations in Tableau. One table calc to give each path a unique ID (I'd suggest something simple like a concatenated string of "origin" + "destination"), and then make the map.
1 of 1 people found this helpful
For reference - I just tried out the data prep option in the Maestro Beta - it took about three minutes (only this long because I hadn't used it in a while and forgot where some of the buttons were located...it would be faster otherwise). The prep flow looks like this:
Once the data was prepped, I opened in Tableau. Created a calculated field for the STATE to use in the path (simple calculation: if [Pivot1 Values] = 'Orig' then [From State] else [to State] end)
Then I had a flow map: