How do I parse column data into separate row for each unique sets of values?
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.sprint.Sprint@54472d2e[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=],com.atlassian.greenhopper.service.sprint.Sprint@7d75068c[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=]
Best Answer
-
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!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
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!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive