Unable to extract substring from comma delimited field (within a dataset)


I’m new to AWS Quicksight. I created a dataset based on an imported file (from another cloud service provider). The dataset includes a “tags” field, which is a comma delimited string. Within that dataset, I’m trying to create a calculated field to extract the “costcenter” value from this “tags” field. I’ve tried multiple string functions (including substring and locate), and thus far haven’t been successful. I’d appreciate any ideas on how I can extract the “costcenter” value into its own field. Is this even possible? For each record, the position of the “costcenter” key/value pair (within the “tags” field) isn’t always consistent, nor is the value’s length.

Here are a few sample values from this “tags” field:
Value/Record 1: “costcenter”: “123456789”,“description”: “Test environment”,“workload”:“core”
Value/Record 2: “description”: “test environment”,“costcenter”: “22222”,“maintenance”:“none”
Value/Record 3: “maintenance”: “none”,“description”: “dev environment”,“costCenter”: “444444”

Thanks in advance!! :slight_smile:

1 Like

Welcome to the Community @jrooney! I am pinging some of our team to see if we can get an answer for you on this. Thanks, and let us know how we can help you on your learning journey!

1 Like

Hi @jrooney - This is a very interesting problem and thanks for posting it. Can you please create a calculated field with below logic and see whether it is fulfilling your requirement or not.


See sample input and output below.

Regards - Sanjeeb

1 Like

Hi @jrooney - Is the above command worked for you ? If yes, please confirm and mark it as solution so that it can help other community members.

If not, give the error details so that we can explore more on this.

Have a great week ahead.

Regards - Sanjeeb

It works!!! Thanks @Sanjeeb2022 and @Kristin !! Much appreciated!!! :slight_smile: