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

    Splitting Cell Data into a new row

    David Kwiatkowski
      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

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

           

          regards

           

          Marco

            • Re: Splitting Cell Data into a new row
              David Kwiatkowski

              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:

                 

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

              • 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
                  sreemannarayana g

                  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

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

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

                   

                  Thanks,
                  Sreeman.