Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting Cell Data into a new row

Case #Participant NamePrimary ResultRole 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

5 Replies
MarcoWedel

‌please post a sample file to test solutions with.

Maybe subfield() could solve your issue.

regards

Marco

avinashelite

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

Not applicable
Author

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.

Not applicable
Author

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.

MarcoWedel

maybe like this:

QlikCommunity_Thread_214615_Pic1.JPG

QlikCommunity_Thread_214615_Pic2.JPG

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