How do I parse column data into separate row for each unique sets of values?

Dot_Perez

I have a Jira Dataset using the Jira Connector. The Sprint column contains a string of data like below. If a Jira story carries over from one sprint to another, the new sprint details are appended to the data string.

Currently I am identifying stories in an 'Active' sprint by including records with %ACTIVE%. I would like the dataset to have one row for each sprint, Closed or Open that has the sprint name, startDate and endDate for each sprint in separate columns

I am able to comma separate the string and use formulas to isolate the sprint name, startDate, endDate, but my output is showing me the Active sprint name, with the first instance of the startDate, endDate...

I've been playing with regex, but when using, I'm only getting the first instance of the data in my returned results.

com.atlassian.greenhopper.service.sprin[email protected][id=1646,rapidViewId=241,state=CLOSED,name=ICT Sprint 3,startDate=2022-01-31T17:35:00.000-05:00,endDate=2022-02-11T03:35:00.000-05:00,completeDate=2022-02-14T15:31:45.531-05:00,sequence=1646,goal=],[email protected][id=1815,rapidViewId=241,state=ACTIVE,name=ICT Sprint 8,startDate=2022-04-11T15:48:36.878-04:00,endDate=2022-04-22T01:48:00.000-04:00,completeDate=<null>,sequence=1815,goal=]

jaeW_at_Onyx

looks like you could split your data using a comma delimiter. have a look at the split_part() function in MagicETL > Formula tile

from there you can use the same techniques laid out in this video, https://www.youtube.com/watch?v=oYcpYE7DiV4.

if you need to start on just the content between the square brackets [] then start with regex for that.

from there you can split_part on = or use the RIGHT() function to find everything after the first = sign.

tons of options!

jaeW_at_Onyx

looks like you could split your data using a comma delimiter. have a look at the split_part() function in MagicETL > Formula tile

from there you can use the same techniques laid out in this video, https://www.youtube.com/watch?v=oYcpYE7DiV4.

if you need to start on just the content between the square brackets [] then start with regex for that.

from there you can split_part on = or use the RIGHT() function to find everything after the first = sign.

tons of options!

Dot_Perez

Thank you @jaeW_at_Onyx. The video was very helpful. I was able to use this method to pull out the most recent sprint data and get the right details parsed. I've created a recursive dataflow after adding a run_date field. This looks promising.