I am trying to get a count of instance per keyword which is in a keywords column. The column can contain no data, a single keyword, or multiple seperated by ;
The way I am trying to get there is by inserting duplicate rows. For example, if the keywords are value1; value2 I will end up with two copies of that row, one with value1 as keyword and one with value2. I can then group or pivot to get the value counts.
I have a partial solution; the data flows to a bunch of java snippets which all look like this:
int Position = 1;
String Tokens = $Keywords$.split(";");
if (Tokens.length < Position) return null;
else return Tokens[Position-1].trim();
The snippets are all the same except Position. I then filter null values and concatenate. I have to create as many snippets as there are number of keywords.
I looked at the cell splitter but it doesn't quite solve the problem of the final pivot since all my data is in different columns.
Any ideas? Thanks!