topic Splitting Cell Data into a new row in QlikView

Splitting Cell Data into a new row

Tue, 26 Apr 2016 19:13:22 GMT

Case #	Participant Name	Primary Result	Role in Case
12345	1. John Doe 2. Jane Doe	1. Termination 2. First Written Warning	1. Implicated Person 2. Implicated Person
56789	1. Mike Miller 2. John Smith 3. Joe Montana	1. Unsubstantiated 2. Unsubstantiated 3. Coaching/Informal Guidance	1. Implicated Person 2. Implicated Person 3. Implicated Person

Case #

Participant Name

Primary Result

Role in Case

12345

1. John Doe

2. Jane Doe

1. Termination

2. First Written Warning

1. Implicated Person

2. Implicated Person

56789

1. Mike Miller

2. John Smith

3. Joe Montana

1. Unsubstantiated

2. Unsubstantiated

3. Coaching/Informal Guidance

1. Implicated Person

2. Implicated Person

3. Implicated Person

I have a spreadsheet with cells similar to the above table. I need to be able to do a count of how many results we have (5 terminations, 3 first written warnings etc). The cells participant name, primary result and role in case have multiple lines in one cell. Is there a way to split this data out so that they are on a new line (row) but maintain the case #? Or can I keep them as they are but still count by term?

My final outcome is that I need to count the number of unsubstantiated, coaching, verbal warning, first written warning, resignation and termination counts for the report. Generally each case number will have 1-4 of these primary results because multiple people were associated with the event.

I wasn't sure if this was something I would do when loading the data.

Thanks,

Dave

Re: Splitting Cell Data into a new row

MarcoWedel — Tue, 26 Apr 2016 20:17:43 GMT

‌please post a sample file to test solutions with.

Maybe subfield() could solve your issue.

regards

Marco

Re: Splitting Cell Data into a new row

avinashelite — Wed, 27 Apr 2016 11:14:20 GMT

please provide sample data along with the desired output format so that it will be helpful for us to understand

Re: Splitting Cell Data into a new row

Wed, 27 Apr 2016 13:15:15 GMT

Thanks for the help. I've attached a sample data set. The first tab is what would be loaded into QV. My goal would be to do a pivot table to get the counts needed to generate what is found in columns AD-AM on the second tab. Column X is really the only column that I need to split the data. I tried SubField but I don't have a good delimiter that I can use.

Re: Splitting Cell Data into a new row

Wed, 27 Apr 2016 18:43:27 GMT

Hi David,

In your query column X is the only thing you need to split right. Then could you please see the below script and let me know this is you were expecting and anything I missed out. I splitted the column X.

Load subfield(replace([Primary Participant Result],CHR(10),';'),';') as [Primary Participant Result]

From

FROM

(ooxml, embedded labels, table is [-Load into QV]);

Thanks,
Sreeman.

Re: Splitting Cell Data into a new row

MarcoWedel — Wed, 27 Apr 2016 21:52:58 GMT

maybe like this:

SET vSplitRows = If(SubField($1,Chr(10),IterNo()) like IterNo()&'. *', SubField(SubField($1,Chr(10),IterNo()),IterNo()&'. ',2), SubField($1,Chr(10),IterNo()));

table1:
LOAD RecNo() as ID,
     [Assigned Tier], 
     [Organization/Building name], 
     [Location/Address], 
     City, 
     [State/Province], 
     Country, 
     [Case Number], 
     Issue, 
     [Case Status], 
     [Intake Method], 
     [Date Opened], 
     [Date Closed], 
     [Case Creator], 
     Details, 
     Product, 
     Significant, 
     [Impact Rating], 
     [Primary Case Outcome], 
     [Action Taken], 
     [Primary Assignee Function]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1027906-223217/SAMPLE_Closed%20Case%20Report%20Q1%202016%20Data%20Only.xlsx] (ooxml, embedded labels, table is [-Load into QV]);

table2:
LOAD RecNo() as ID,
     IterNo() as SeqNo,
     $(vSplitRows([Participant Name])) as [Participant Name],
     $(vSplitRows([Relationship to Organization/Case])) as [Relationship to Organization/Case],
     $(vSplitRows([Role in Case])) as [Role in Case],
     $(vSplitRows([Primary Participant Result])) as [Primary Participant Result]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1027906-223217/SAMPLE_Closed%20Case%20Report%20Q1%202016%20Data%20Only.xlsx] (ooxml, embedded labels, table is [-Load into QV])
While IterNo()<=RangeMax(SubStringCount([Participant Name],Chr(10)),SubStringCount([Relationship to Organization/Case],Chr(10)),SubStringCount([Role in Case],Chr(10)),SubStringCount([Primary Participant Result],Chr(10)))+1;

hope this helps

regards

Marco