Hi,
I have a data table from an external source with two fields. Field one is a unique ID. Field 2 is a list of categories in the format "category1; category2; category 3..." There can be just one category or many. They are always seperated by a semicolon but not always sequential.
Example:
001 category1; category2
002 category2; category5
003 category18
004 category23; category1; category11; category6
I would like to tranform this table into an easier format to work with such as:
001 category1
001 category2
002 category2
002 category5
003 category18
004 category23
004 category1
004 category11
004 category6
However the way I'm currently doing these feel really ineffeicent. I seperate the category field delimited by semi collon, giving me many collumns: "category 1, category 2...". Most of the values in the later collumns are nulls but some rows have a large number of categories. I then duplicate the table once for each new collumn, and delete all collumns in each duplicate except one.
ie 'table 1' has only [ID] and [category 1], 'table 2' has only [ID] and [category2]...
Once I have a table for every collumn I apend all the tables into the final result.
This has three main problems:
- my dataset now has a large number of tables which I don't want (I only want to refernce the final apended table.)
- the data is not auto refreshing - if an extra category is added in the base data which exceeds my number of split collumns it won't be picked up.
- It takes absolutey ages to duplicate and sift tables like this.
I would really appreciate any better solution than the one I'm using. Thanks!
