Forum Discussion
Data organization for multiple vendors
I am trying to figure out how to organize by excel data based off of states and what general contarctors work in those states. I eventually want to create a map visual where I click on a state and the general contractors will be shown.
3 Replies
I would start with two tables.
1, all the states with a column of the state name, a column of the state short code.
2, all the contractors with a column of their name/company name, their state short code, address, any other details specific to them.
I connected those tables in relationship view where StateShort and ContractorState (also short) are matched up.
Then added a map visual with StateShort, as well as some Matrixes to list their names. You can see each state with Contractors has a dot on the map. When I click New York, the tables filter to New York and show only those contractors.
Just a rough idea how to begin. Hope this helps.
For the sake of keeping it organized and easier to manage in the future (and allow one-to-many relationships to work), I would do a table that is just a unique list of contractors (like the one above). Then make another table (third one now) that is something like "ServiceAreas" which will have one contractor & state per row, and it is ok to have multiple rows of the same contractor, one for each state they service.