5 Replies Latest reply: Apr 27, 2016 5:52 PM by Marco Wedel RSS

    Splitting Cell Data into a new row

      Case #Participant NamePrimary ResultRole in Case

      1. John Doe

      2. Jane Doe

      1. Termination

      2. First Written Warning

      1. Implicated Person

      2. Implicated Person


      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.




        • Re: Splitting Cell Data into a new row
          Marco Wedel

          please post a sample file to test solutions with.

          Maybe subfield() could solve your issue.





            • Re: Splitting Cell Data into a new row

              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
                Marco Wedel

                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()));
                LOAD RecNo() as ID,
                     [Assigned Tier], 
                     [Organization/Building name], 
                     [Case Number], 
                     [Case Status], 
                     [Intake Method], 
                     [Date Opened], 
                     [Date Closed], 
                     [Case Creator], 
                     [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]);
                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





              • Re: Splitting Cell Data into a new row
                Avinash R

                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

                  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]



                  [C:\Users\snar14\Desktop\Copy of SAMPLE_Closed Case Report Q1 2016 Data Only.xlsx]

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