topic Re: Picking a particular values form string and separated by comma from a field value in QlikView

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

Qlik-Developer — Sat, 16 Nov 2024 18:01:18 GMT

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.....

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

Kushal_Chawda — Fri, 02 Oct 2020 20:53:06 GMT

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

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

Qlik-Developer — Fri, 02 Oct 2020 21:14:19 GMT

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.

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

Qlik-Developer — Fri, 02 Oct 2020 21:17:03 GMT

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

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

Kushal_Chawda — Fri, 02 Oct 2020 21:19:32 GMT

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

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

Qlik-Developer — Fri, 02 Oct 2020 21:21:05 GMT

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.

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

Kushal_Chawda — Fri, 02 Oct 2020 23:38:27 GMT

@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);

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

Qlik-Developer — Sat, 03 Oct 2020 17:55:27 GMT

Thanks for your reply kush.

Max_cnt table should be resident of Data table right?

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

Qlik-Developer — Sat, 03 Oct 2020 18:09:57 GMT

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.

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

Kushal_Chawda — Sat, 03 Oct 2020 20:52:39 GMT

@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

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

Kushal_Chawda — Sat, 03 Oct 2020 20:54:26 GMT

@Qlik-Developer Your sprint filed will be comma separated values of the sprints so do you want to show first YBS sprint of that string?

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

Qlik-Developer — Sun, 04 Oct 2020 08:30:23 GMT

Yes values of sprint should be separated by comma .

No all the sprints for particular userstory should be shown in sprint field separated by comma.

In bar chart only active sprint should be shown, in upcomimg days sprint will change so bar chart should change according to active sprints.

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

Brett_Bleess — Thu, 22 Oct 2020 14:28:59 GMT

@Kushal_Chawda poster did reply to your last set of questions, sorry the notifications are still screwing up, if you get a chance to circle back, appreciate it.

Regards,
Brett