Data shaping for shape maps

[Maexchen]

Hi,

I have a dataset that contains rows with Products (each with a unique identifier) which in one column contains all the countries where this product is available - this field is comma deliminated (e.g. "USA, UK, Netherlands" or "UK, Australia, New Zealand"). How do I shape this data so that I can show it in a shape map?

I do have the shape map set up, the countries map to names of the map but currently I only see those datasets that have only one country in the country field and the others don't map to the map. What I would like to have is that when I click on one of the datasets it highlights all the countries and if I click on a country it shows all corresponding rows. 

I tried to split the country field into multiple columns but I can only use one column in the shape map visual. (also some products have >30 countries so this a bit messy)

I would appreciate some help with this (I hope i'm in the right place). thanks.

[jthomson]

---

I tried to split the country field into multiple columns but I can only use one column in the shape map visual. (also some products have >30 countries so this a bit messy)

---

Did you try to unpivot the multiple columns into one once you split them?

[Maexchen]

Hi,

no I haven't but I will try. However, will that not multiply all the rows? I have another visual that counts another column, which will then show me too many(?), although I can probably get around that by counting (distinct).

I'll try that

[Maexchen]

any other suggestions? unfortunately that did not work, it still only shows one country per data set

[Maexchen]

*bump*

unpivoting the data makes them all to be in one column but therefore of course adds a lot of rows. Is there an option to create a linked table?

[Dave1]

hi, i am just working on the same issue. I resolve the issue by creating a supporting table and then link the table based on the PK. You can create a link table using a summarizecolumns functions. In the query editor mode, duplicate your main table query. Only keep the ID and the column that has the country names. Under Transform ribon, select Split Column by delimiter. After spliting step is done, on the left top side just before your first column, you will see a small table icon. Click on it and then select remove duplicates. Then, load your table. Your supporting table will only have unique country names for each product id. After load is done, go to relationship mode and create the link between your new table and old/main one based on product id.