Using complex logic with comma-separated column values

user04311
user04311 Member
in Archive

Hello! I have a dataset that looks like this (for the purposes of my question, anyway):

ID Categories
1  A
2  A,B
3  B,C

.. and I want to be able to use complex logic on this field. e.g., WHEN Categories IN 'A' should return 2 rows with IDs 1 and 2, or I want to have a bar graph where the X axis is based on the Categories rather than showing combinations of them. To date, the solution has been a dataflow which converts the data into something like this:

ID Categories
1  A
2  A
2  B
3  B
3  C 

This produces undesirable duplicates when looking at the data holistically, vs. per category. For example, with the first dataflow, if I look at row totals I end up at 3, but with the second dataset, it's 5. So, I end up creating a lot of duplicate reports: 1 based on the dataset, for holistic reporting, and 1 based on the dataflow, for per-category reporting. This is costing me a lot of time and complexity. Everytime I tweak one report, I have to tweak the other.. I have to duplicate all my beast modes.. I have to explain to people where you can filter on categories, and where you can't..

 

Is there a smarter way to do this?

Comments

Categories