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

Dot_Perez
Dot_Perez Member
in Magic ETL

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"
  • Dot_Perez
    Dot_Perez Member

    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.

Categories