Picking a particular values form string and separated by comma from a field value

[Qlik-Developer]

  Hi Everyone,

I have loaded issues table data from jira to qliksense. For sprint field ,values are loading with big string which is unwanted,shown below.

Below one is the sprint field value for one user story.where user story started in sprint8 and carry forwarded to  sprint 9.

"com.atlassian.greenhopper.service.sprint.Sprint@51c6f9d7[id=2000,rapidViewId=776,state=CLOSED,name=YBS Sprint 8,startDate=2020-07-30T16:00:38.382Z,endDate=2020-08-14T15:59:00.000Z,completeDate=2020-08-17T17:15:41.490Z,sequence=2002,goal=<null>],com.atlassian.greenhopper.service.sprint.Sprint@68327bc3[id=2001,rapidViewId=776,state=CLOSED,name=YBS Sprint 9,startDate=2020-08-17T15:19:40.545Z,endDate=2020-08-26T17:19:00.000Z,completeDate=2020-08-27T16:19:51.655Z,sequence=2004,goal=<null>]".

So from above string i want to pick ,where ever name= YBS Sprint along with sprint number.

For example: 1). For user story 10 ,actual sprint is 8 and carry forward to sprint 9

                    2) . For user stroy 15,actual sprint is 9 and carry forward to sprint 10 and sprint 11

Finaly out put i am expecting is:

Userstory,sprint

10,YBS Sprint8, YBS Sprint9

15,YBS Sprint9, YBS sprint 10, YBS sprint11

Sprint field should come with values separated by comma instead of string, may be in future if sprints is carry forward to another sprint for userstory that sprint also should be separated by comma.

Can any one suggest your inputs how to proceed on this...

Thanks in advance.....

[Kushal_Chawda]

@Qlik-Developer  How do I find User story? Also How do you know for that user story sprint is carry forwarded?

[Qlik-Developer]

Data set gives the userstory across sprints and sprints carryforward.

The string which i provided is sprint field value for one user story, similary all the user stories havng sprints values in string format.

New=YBS sprint 8 with start date and end date, new = YBS sprint9 with start and end date.

[Qlik-Developer]

---

@Kushal_Chawda wrote:

@Qlik-Developer  How do I find User story? Also How do you know for that user story sprint is carry forwarded?

---

[Kushal_Chawda]

@Qlik-Developer  By mistake you accepted solution to my previous reply.

[Qlik-Developer]

For example: data set looks like below

Table:

Userstory,sprint,story points

10, string, 5

15, string,4

16, string,2

In above dataset string means the one which is provided in orginal post.varies for every userstory.

[Kushal_Chawda]

@Qlik-Developer  try below

Data:
LOAD  RecNo() as Records,
      UserStory,
      StoryPoints
      Sprint,
      SubStringCount(lower(trim(Sprint)),'=ybs') as cnt_ybs
FROM
[C:\Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

// Get the max count of "YBS" words in Sprint field to loop 
Max_cnt:
LOAD max(cnt_ybs) as max_cnt_ybs;
LOAD FieldValue('cnt_ybs',RecNo()) as cnt_ybs
AutoGenerate FieldValueCount('cnt_ybs');

let vMax_cnt_ybs = Peek('max_cnt_ybs',0,'Max_cnt');

DROP Table Max_cnt;

YBS_Sprint:
LOAD Records,
     concat('YBS '&YBS_Sprint,',') as YBS_Sprint
where len(trim(YBS_Sprint))>0
Group by Records;
LOAD Records,
     TextBetween(lower(trim(Sprint)),'=ybs',',',IterNo()) as YBS_Sprint
Resident Data
While IterNo()<=$(vMax_cnt_ybs);

[Qlik-Developer]

Thanks for your reply kush.

Max_cnt table should be resident of Data table right?

[Qlik-Developer]

Hi, 

One more doubt, want to create bar chart with dimension is sprints field nd measure is count of user stories.

But x axis should show current sprints..suppose now 12th sprint is active so chart should show only 12th sprint data across measure.in upcoming days sprint becomes to 13 so that time chart should show 13th sprint data alone.

[Kushal_Chawda]

@Qlik-Developer  no need to take resident. You can simply copy paste that script and just change the actual field name in fieldvalue and fieldvaluecount functions