2 Replies Latest reply: Aug 19, 2016 2:54 PM by KHOA NGUYEN RSS

    Qlik data load -- Multiple rows into a comma-delimited value

    KHOA NGUYEN

      I have an Excel sheet with data like this:

       

      CUSIPCounterParty
      1234ABCCounterparty 1
      1234ABCCounterparty 2
      1234ABCCounterparty 3

       

      Need output

       

      CUSIPCounterparties
      1234ABCCounterparty 1, Counterparty 2, Counterparty 3

       

      In the data load scripts (or other other methods?), how can I collapse the rows into a comma delimited field?

       

      I need to do this in the load scripts because I have other tables that depend on the uniqueness of CUSIP and I need to collapse the rows.

        • Re: Qlik data load -- Multiple rows into a comma-delimited value
          Michael Tarallo

          Hi Khoa,

           

          Try this:

           

          [rawData]:
          LOAD CUSIP,
              CounterParty
          FROM
          [C:\Users\mtarallo\Desktop\Book1.xlsx]
          (ooxml, embedded labels, table is Sheet1);
          
          
          Second:
          NoConcatenate LOAD *
          Resident rawData
          Order By CUSIP;
          DROP Table rawData;
          
          New:
          NoConcatenate LOAD
              CUSIP,
              Concat(CounterParty, ',') as CounterParties
          
          Resident Second
          Group By CUSIP;
          DROP Table Second;
          
          

           

          Note the output:

           

           

          I attached a QlikView sample here and also a Qlik Sense sample in the other post.

           

          Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

           

          Regards,

          Mike Tarallo

          Qlik

          • Re: Qlik data load -- Multiple rows into a comma-delimited value
            Michael Tarallo

            Here is a Qlik Sense sample - attached:

             

            If using Qlik Sense Desktop. please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5

             

            NOTE the FROM clause uses the lib:// syntax connecting to a Folder Connection named "Desktop"

             

             

            [rawData]:
            LOAD
                CUSIP,
                CounterParty
            FROM [lib://Desktop/Book1.xlsx]
            (ooxml, embedded labels, table is Sheet1);
            
               
                  
            Second:  
            NoConcatenate LOAD *  
            Resident rawData  
            Order By CUSIP;  
            DROP Table rawData;  
                  
            New:  
            NoConcatenate LOAD  
            CUSIP,  
            Concat(CounterParty, ',') as CounterParties  
            Resident Second  
            Group By CUSIP;  
            DROP Table Second;  
            

             

             

             

            Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

             

            Regards,

            Mike Tarallo

            Qlik